Help Please - TransferSpreadsheet

Question

Hi all:

Can on you Access gurus answer a quick question for me.

I have a database that is split between a front end (PoolFE) and a back end (PoolBE).  I have a form in the front end that I am trying to create some code on to import an Excel spreadsheet as an Access table.  I would like to import it into the back end database.  I have used TranferSperadSheet to import spreadsheets into the front end of databases but never the back end.  Can TransferSpreadSheet be used to import into a back end database or is it a case it only works for the current or open database?  If it can be done what would the code look like?

Any and all help is greatly appreciated.

Thanks.

Answer 1

Too lazy to test, but DoCmd is a method of the Application object, so you might be able to instantiate an instance of the Access Application, use its OpenCurrentDatabase method to point to the back-end and go from there.

Something like:

Dim app As Object

  Set app = CreateObject("Access.Application")

  app.OpenCurrentDatabase "\SomeServer\SomeShare\SomeFolder\SomeFile.accdb"

  app.DoCmd.TransferSpreadsheet ....

Answer 2

If all you need to be able to do is read the data then linking to the Excel worksheet makes more sense than importing.  If you need to be able to edit the data in Access then also create a table in the back end and, to keep it up-to-date (a) delete all rows from it by means of Delete query and (b) refill it by means of an Append query based on the linked Excel table.

Answer 3

Doug:

Thanks for the help.  Seeing your and Ken's answers I am wondering if I am making this harder than it has to be.  My goal was trying not to have any table saved as part of the front end database.  Where this Excel file is coming from a source outside of our company and is being updated at least once a day I am now thinking maybe what I should just do is set up a "link " to the file in my front end and  be done with it.

Does this make sense?

Thanks,

Darren

Answer 4

Ken:

Thanks for the help.

Darren

Answer 5

As far as I'm aware the TransferSpreadSheet method can only refer to the current database.  However, you could try creating a Public function in the back end file which calls the method.  Then create a reference to the back end file (Tools | References on the VBA menu bar).  This will expose the function in the front end, so you can then call it there to, hopefully, import the spreadsheet into the back end.  I've never tried it, but see no reason why it shouldn't work.