Changing Path for table linked to spreadsheet

[franc sutherland]

Hi everyone,

I am using Access 2003. I have a database with a table in it which is
linked to an excel spreadsheet. When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.

Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?

I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format. I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.

Lots of thanks in advance,

Franc.

[Tom van Stiphout]

Re: Changing Path for table linked to spreadsheet

On Tue, 21 Oct 2008 17:28:58 -0700 (PDT), franc sutherland
<franc.sutherla nd@googlemail.c omwrote:

You'd want to read up on RefreshLink a bit more. This function in
combination with the Connect property is how you can change links at
runtime. You can indeed use a constant for the location, or use a
File/Open dialog.

Perhaps this article will help:

http://www.mvps.org/access/tables/tbl0012.htm

And bookmark that site!

-Tom.
Microsoft Access MVP

>Hi everyone,
>
>I am using Access 2003. I have a database with a table in it which is
>linked to an excel spreadsheet. When I install the database on
>someone else's system, the pathname to the excel file is different.
>Howwever, as it is in runtime on their system, I can't update the link
>using the Linked Table Manager.
>
>Is there a way of using a public constant to store a path which can be
>altered in advance and then combined with a startup module to change
>the link path?
>
>I have tried using the rRefreshLink function, but it gives me error
>3343, Unrecognised database format. I think this function is aimed at
>re-freshing links to BE mdb files and doesn't work with tables linked
>to excel spreadsheets.
>
>Lots of thanks in advance,
>
>Franc.

[jonceramic]

Re: Changing Path for table linked to spreadsheet

On Oct 21, 7:28 pm, franc sutherland <franc.sutherl. ..@googlemail.c om>
wrote:

Hi everyone,
>
I am using Access 2003.  I have a database with a table in it which is
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.
>
Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?
>
I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.
>
Lots of thanks in advance,
>
Franc.

Franc,

I have an application where I point to multiple spreadsheets based on
standard rules.

In this case, each plant has a folder, and each year of the survey has
its own folder, and each file has the same name. And, I use a generic
table named xlimportsurvey as my link.

So, they choose cmbSurvey and cmbPlantChooser , and I build the file
location as strFileLocation . You, of corse, could just set
strFileLocation as the generic variable.
-----------------------------------------------------------------------------------------------------------
Dim strFileLocation As String

If Not IsNull(Me.cmbSu rvey) And Not IsNull(Me.cmbPl antChooser) Then

strFileLocation = "\\srvr\share\P rojects\Survey\ " & Me.cmbSurvey &
_
"\" & Me.cmbPlantChoo ser & "\sheet_1.x ls"

' This IfThen deletes the old table if it exists because if you don't,
then it just creates a new link with a "1" appended to the end.
If DCount("*", "MsysObject s", "[Name]= 'xlimportsurvey '") Then
DoCmd.DeleteObj ect acTable, "xlimportsurvey "
End If

'This line reestablishes the link to the new location.
DoCmd.TransferS preadsheet acLink, acSpreadsheetTy peExcel8,
"xlimportsurvey ", _
strFileLocation , False, "A3:AG1000"

End If

Me.RecordSource = "qrysurvey" 'This query uses xlimportsurvey as one
of its tables. So, resetting the recordsource is needed, not just a
requery.

'These requeries are for listboxes which need to be repopulated. Not
really relevant to your issue.
Me.Combo46.Requ ery
Me.Combo48.Requ ery
Me.List52.Reque ry

-------------------------------------------------------------------------
Yah, I was lazy and didn't rename Combo46, Combo48, and List52 during
development... :) Crazy the stuff you find when you go back and look
at your old code.

All thanks to whichever Access Gods had the transferspreads heet method
documented on their websites. I found 3 or 4 references to it once I
hit on the right google search.

Jon

[bobh]

Re: Changing Path for table linked to spreadsheet

On Oct 21, 8:28 pm, franc sutherland <franc.sutherl. ..@googlemail.c om>
wrote:

Hi everyone,
>
I am using Access 2003.  I have a database with a table in it which is
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.
>
Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?
>
I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.
>
Lots of thanks in advance,
>
Franc.

Did you use UNC to link your tables/spreadsheets ???
bobh.

[franc sutherland]

Re: Changing Path for table linked to spreadsheet

On Oct 22, 2:40 pm, Tom van Stiphout <tom7744.no.s.. .@cox.netwrote:

On Tue, 21 Oct 2008 17:28:58 -0700 (PDT), franc sutherland
>
<franc.sutherl. ..@googlemail.c omwrote:
>
You'd want to read up on RefreshLink a bit more. This function in
combination with the Connect property is how you can change links at
runtime. You can indeed use a constant for the location, or use a
File/Open dialog.
>
Perhaps this article will help:http://www.mvps.org/access/tables/tbl0012.htm
And bookmark that site!
>
-Tom.
Microsoft Access MVP
>

Hi everyone,

>

I am using Access 2003.  I have a database with a table in it which is
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.

>

Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?

>

I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.

>

Lots of thanks in advance,

>

Franc.

Hi Tom,

Thanks for the link. Very useful stuff indeed.

It's bookmarked!

All the best,

Franc.

[franc sutherland]

Re: Changing Path for table linked to spreadsheet

On Oct 22, 5:56 pm, jonceramic <joncera...@gma il.comwrote:

On Oct 21, 7:28 pm, franc sutherland <franc.sutherl. ..@googlemail.c om>
wrote:
>
>
>

Hi everyone,

>

I am using Access 2003.  I have a database with a table in it which is
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.

>

Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?

>

I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.

>

Lots of thanks in advance,

>

Franc.

>
Franc,
>
I have an application where I point to multiple spreadsheets based on
standard rules.
>
In this case, each plant has a folder, and each year of the survey has
its own folder, and each file has the same name.  And, I use a generic
table named xlimportsurvey as my link.
>
So, they choose cmbSurvey and cmbPlantChooser , and I build the file
location as strFileLocation .  You, of corse, could just set
strFileLocation as the generic variable.
-----------------------------------------------------------------------------------------------------------
Dim strFileLocation As String
>
If Not IsNull(Me.cmbSu rvey) And Not IsNull(Me.cmbPl antChooser) Then
>
    strFileLocation = "\\srvr\share\P rojects\Survey\ " & Me.cmbSurvey &
_
                        "\" & Me.cmbPlantChoo ser & "\sheet_1.x ls"
>
' This IfThen deletes the old table if it exists because if you don't,
then it just creates a new link with a "1" appended to the end.
    If DCount("*", "MsysObject s", "[Name]= 'xlimportsurvey '") Then
        DoCmd.DeleteObj ect acTable, "xlimportsurvey "
    End If
>
'This line reestablishes the link to the new location.
    DoCmd.TransferS preadsheet acLink, acSpreadsheetTy peExcel8,
"xlimportsurvey ", _
                                strFileLocation , False, "A3:AG1000"
>
End If
>
Me.RecordSource = "qrysurvey"  'This query uses xlimportsurvey as one
of its tables.  So, resetting the recordsource is needed, not just a
requery.
>
'These requeries are for listboxes which need to be repopulated.  Not
really relevant to your issue.
    Me.Combo46.Requ ery
    Me.Combo48.Requ ery
    Me.List52.Reque ry
>
-------------------------------------------------------------------------
Yah, I was lazy and didn't rename Combo46, Combo48, and List52 during
development...  :)  Crazy the stuff you find when you go back and look
at your old code.
>
All thanks to whichever Access Gods had the transferspreads heet method
documented on their websites.  I found 3 or 4 references to it once I
hit on the right google search.
>
Jon

Hi Jon,

Thanks for that. It is a very different approach and it is always
good to learn about all the different ways that are possible in
Access. I was wondering in your scenario, with the two combo boxes
which build the file pathname string. Does the combo box look up the
file directory and list the options, or do you keep a table with the
names?

All the best,

Franc,

[franc sutherland]

Re: Changing Path for table linked to spreadsheet

On Oct 22, 5:59 pm, bobh <vulca...@yahoo .comwrote:

On Oct 21, 8:28 pm, franc sutherland <franc.sutherl. ..@googlemail.c om>
wrote:
>
>
>

Hi everyone,

>

I am using Access 2003.  I have a database with a table in it which is
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.

>

Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?

>

I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimed at
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.

>

Lots of thanks in advance,

>

Franc.

>
Did you use  UNC  to link your tables/spreadsheets ???
bobh.

Hi Bob,

Yes, I used \\server\folder \subfolder\ etc....

All the best,

Franc.

[jonceramic]

Re: Changing Path for table linked to spreadsheet

On Oct 23, 12:29 pm, franc sutherland
<franc.sutherl. ..@googlemail.c omwrote:

On Oct 22, 5:56 pm, jonceramic <joncera...@gma il.comwrote:
>
>
>
>
>

On Oct 21, 7:28 pm, franc sutherland <franc.sutherl. ..@googlemail.c om>
wrote:

>

Hi everyone,

>

I am using Access 2003.  I have a database with a table in it whichis
linked to an excel spreadsheet.  When I install the database on
someone else's system, the pathname to the excel file is different.
Howwever, as it is in runtime on their system, I can't update the link
using the Linked Table Manager.

>

Is there a way of using a public constant to store a path which can be
altered in advance and then combined with a startup module to change
the link path?

>

I have tried using the rRefreshLink function, but it gives me error
3343, Unrecognised database format.  I think this function is aimedat
re-freshing links to BE mdb files and doesn't work with tables linked
to excel spreadsheets.

>

Lots of thanks in advance,

>

Franc.

>

Franc,

>

I have an application where I point to multiple spreadsheets based on
standard rules.

>

In this case, each plant has a folder, and each year of the survey has
its own folder, and each file has the same name.  And, I use a generic
table named xlimportsurvey as my link.

>

So, they choose cmbSurvey and cmbPlantChooser , and I build the file
location as strFileLocation .  You, of corse, could just set
strFileLocation as the generic variable.
-----------------------------------------------------------------------------------------------------------
Dim strFileLocation As String

>

If Not IsNull(Me.cmbSu rvey) And Not IsNull(Me.cmbPl antChooser) Then

>

    strFileLocation = "\\srvr\share\P rojects\Survey\ " & Me.cmbSurvey &
_
                        "\" & Me.cmbPlantChoo ser & "\sheet_1.x ls"

>

' This IfThen deletes the old table if it exists because if you don't,
then it just creates a new link with a "1" appended to the end.
    If DCount("*", "MsysObject s", "[Name]= 'xlimportsurvey '") Then
        DoCmd.DeleteObj ect acTable, "xlimportsurvey "
    End If

>

'This line reestablishes the link to the new location.
    DoCmd.TransferS preadsheet acLink, acSpreadsheetTy peExcel8,
"xlimportsurvey ", _
                                strFileLocation , False, "A3:AG1000"

>

End If

>

Me.RecordSource = "qrysurvey"  'This query uses xlimportsurvey as one
of its tables.  So, resetting the recordsource is needed, not just a
requery.

>

'These requeries are for listboxes which need to be repopulated.  Not
really relevant to your issue.
    Me.Combo46.Requ ery
    Me.Combo48.Requ ery
    Me.List52.Reque ry

>

-------------------------------------------------------------------------
Yah, I was lazy and didn't rename Combo46, Combo48, and List52 during
development...  :)  Crazy the stuff you find when you go back and look
at your old code.

>

All thanks to whichever Access Gods had the transferspreads heet method
documented on their websites.  I found 3 or 4 references to it once I
hit on the right google search.

>

Jon

>
Hi Jon,
>
Thanks for that.  It is a very different approach and it is always
good to learn about all the different ways that are possible in
Access.  I was wondering in your scenario, with the two combo boxes
which build the file pathname string.  Does the combo box look up the
file directory and list the options, or do you keep a table with the
names?
>
All the best,
>
Franc,- Hide quoted text -
>
- Show quoted text -

Well, in my case, I'm downloading a flat excel file from a commerical
survey website. And, I have to do this manually. So, since I have
only 5 folder and I'm the one doing it, I just made it happen manually
by seeding my own lists and using names I create. I never looked into
automating that side of it.

BTW, I realized my raw code is confusing. If I take out the code to
the essence, you're left with this...

strFileLocation = "\\server\folde r\" & strMycustomfold er &
"\subfolder \" & strMycustomfile name
strTableName = "mytablenam e"

If DCount("*", "MsysObject s", "[Name]= '"& strTableName & "'")
Then
DoCmd.DeleteObj ect acTable, strTableName
End If

DoCmd.TransferS preadsheet acLink, acSpreadsheetTy peExcel8,
strTableName _
strFileLocation , False, "A3:AG1000"

Me.RecordSource = strTableName

(NOTE: I use A3:AG1000 as my source because that's where my data
happens to be. You'll obviously pick something different. I think
general Excel conventions work here if you have multiple tabs.)

Jon