Linked tables and Transferspreadsheet/transferdatabase

[ogg13]

I recently used the linked table manager on my database and was quite pleased with the results, until now.

I have to do an excel export from one of the backend linked tables and im not sure how to do it. Is there a way to tell the transferspreadsheet and transferdatabase commands to look outside of the current database?

My only other option is to make temporary tables in the front end on the fly.. which i really wouldnt mind doing, however I cant seem to find a way to allow users to have permissions to do that (using user level security with groups and nothing I seem to do for permissions allows my users to create a table in the frontend.. I can as administrator, but no one else can, even with the exact same permissions as the admin account)...

If anyone can shed light on either of these issues it would be appreciated.

 

[Dennisk]

If the spreadsheet is linked then it is a table that can be referred to just like an access table.

so if the linked spreadsheet is called Sheet1

then you can use 'SELECT * FROM Sheet1'

 

[ogg13]

Let me explain a little bit further.

I am trying to use the following line of code to transfer data out of a table that I have in a backend database. The code is ran from the front end database.

DoCmd.TransferSpreadsheet acExport, , "tblLANPDAExport", FileSelected

The part I put in bold is where im getting errors. Transferspreadsheet on an export expects to see 'tblLANPDAExport' in the current database. It resides in the backend database (I cant figure out how to have the maketable query that creates this table work in the front end due to user permissions I presume)

Basically I need to know how to point the transferspreadsheet command to the backend database and the table within the backend database, or conversly find a way to allow users to create and access tables in the front end database. Currently, only my admin account can create tables in the front end database and I dont know why. No amount of changing permissions on the group or individual level has allowed my users to create tables through maketable queries in the front end.

Thanks!

 

[neileg]

Providing tblLANPDAExport is linked to the current database it doesn't matter that it resides in a different file. If it's not linked, Access can't find it. If you haven't linked it, use File/Get External Data/Link Tables

 

[ogg13]

Well that explains things...

Is there any way to link a table via code easily? The nature of the function that im working with overwrites the table, so technically, for a period of time, the table is deleted, therefore the link is removed for that split second between delete and write.

I guess I could have the function delete the old table that is to be overwritten first, then if I could link the table through code, I could use transferspreadsheet to write the new table to the linked database without linking errors.

Thanks for the explanation... that really clears things up a little. Now if I could just find a way to link to a table in code, ill be in business... Any pointers?

 

[Dennisk]

The Standard way is to use the maketable query once. then change the query to an append query.

Simply run Currentdb.Execute "Delete * from yourtable"
then import.

 

[ogg13]

Thanks! I didnt know why I didnt think of it that way. Cheers!