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]

On Tue, 21 Oct 2008 17:28:58 -0700 (PDT), franc sutherland
<fr**************@googlemail.comwrote:

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]

On Oct 21, 7:28*pm, franc sutherland <[e-mail]>
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.cmbSurvey) And Not IsNull(Me.cmbPlantChooser) Then

strFileLocation = "\\srvr\share\Projects\Survey\" & Me.cmbSurvey &
_
"\" & Me.cmbPlantChooser & "\sheet_1.xls"

' 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("*", "MsysObjects", "[Name]= 'xlimportsurvey'") Then
DoCmd.DeleteObject acTable, "xlimportsurvey"
End If

'This line reestablishes the link to the new location.
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8,
"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.Requery
Me.Combo48.Requery
Me.List52.Requery

-------------------------------------------------------------------------
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 transferspreadsheet method
documented on their websites. I found 3 or 4 references to it once I
hit on the right google search.

Jon

[bobh]

On Oct 21, 8:28*pm, franc sutherland <[e-mail]>
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]

On Oct 22, 2:40*pm, Tom van Stiphout <[e-mail]:

On Tue, 21 Oct 2008 17:28:58 -0700 (PDT), franc sutherland

<[e-mail]:

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]

On Oct 22, 5:56*pm, jonceramic <[e-mail]:

On Oct 21, 7:28*pm, franc sutherland <[e-mail]>
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.cmbSurvey) And Not IsNull(Me.cmbPlantChooser) Then

* * strFileLocation = "\\srvr\share\Projects\Survey\" & Me.cmbSurvey &
_
* * * * * * * * * * * * "\" & Me.cmbPlantChooser & "\sheet_1.xls"

' 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("*", "MsysObjects", "[Name]= 'xlimportsurvey'") Then
* * * * DoCmd.DeleteObject acTable, "xlimportsurvey"
* * End If

'This line reestablishes the link to the new location.
* * DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8,
"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.Requery
* * Me.Combo48.Requery
* * Me.List52.Requery

-------------------------------------------------------------------------
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 transferspreadsheet 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]

On Oct 22, 5:59*pm, bobh <[e-mail]:

On Oct 21, 8:28*pm, franc sutherland <[e-mail]>
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]

On Oct 23, 12:29*pm, franc sutherland
<[e-mail]:

On Oct 22, 5:56*pm, jonceramic <[e-mail]:

On Oct 21, 7:28*pm, franc sutherland <[e-mail]>
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.cmbSurvey) And Not IsNull(Me.cmbPlantChooser) Then

* * strFileLocation = "\\srvr\share\Projects\Survey\" & Me.cmbSurvey &
_
* * * * * * * * * * * * "\" & Me.cmbPlantChooser & "\sheet_1.xls"

' 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("*", "MsysObjects", "[Name]= 'xlimportsurvey'") Then
* * * * DoCmd.DeleteObject acTable, "xlimportsurvey"
* * End If

'This line reestablishes the link to the new location.
* * DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8,
"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.Requery
* * Me.Combo48.Requery
* * Me.List52.Requery

-------------------------------------------------------------------------
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 transferspreadsheet 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\folder\" & strMycustomfolder &
"\subfolder\" & strMycustomfilename
strTableName = "mytablename"

If DCount("*", "MsysObjects", "[Name]= '"& strTableName & "'")
Then
DoCmd.DeleteObject acTable, strTableName
End If

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8,
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