Import Excel file into table reference by DAO

[Dwaine Horton]

I have two database files. One contains a form with my code and I am using
DAO to connect to my database with my tables. What I want to do is have a
user select an Excel file and then I want to import the data from that file
into a table that I created on my database with the tables. I have tried
using transferspreadsheet and it transfer's the data but it puts it in the
wrong database. It is transferring to my database with the form and coding
and not the one with my tables.

How do I get this to transfer the data to my table database?

Here is my code:

Dim wsAccess As Workspace
Dim dbAccess As DAO.Database
Dim tdf As DAO.TableDef
Dim fldAccountName As DAO.Field
Dim fldPhone As DAO.Field
Dim fldAddr1 As DAO.Field
Dim fldAddr2 As DAO.Field
Dim fldAddr3 As DAO.Field
Dim fldAddr4 As DAO.Field
Dim fldCity As DAO.Field
Dim fldState As DAO.Field
Dim fldZip As DAO.Field
Dim blnHasFieldNames As Boolean
Dim strTable As String

'Create the Access workspace
Set wsAccess = DBEngine(0)

'Open a Microsoft Access database
Set dbAccess = wsAccess.OpenDatabase(AccessDataName, False, False)

'If the Account table exists delete it
If IsObject(dbAccess.TableDefs("tblAccount")) Then
dbAccess.TableDefs.Delete "tblAccount"
End If

'Create the table definition in memory
Set tdf = dbAccess.CreateTableDef("tblAccount")

'Create the field definitions in memory
Set fldAccountName = tdf.CreateField("Account_Name", dbText, 50)
Set fldPhone = tdf.CreateField("Site_Phone", dbText, 50)
Set fldAddr1 = tdf.CreateField("Address1", dbText, 50)
Set fldAddr2 = tdf.CreateField("Address2", dbText, 50)
Set fldAddr3 = tdf.CreateField("Address3", dbText, 50)
Set fldAddr4 = tdf.CreateField("Address4", dbText, 50)
Set fldCity = tdf.CreateField("City", dbText, 50)
Set fldState = tdf.CreateField("State", dbText, 2)
Set fldZip = tdf.CreateField("Zip", dbText, 6)

'Append the fields to the TableDef's Field collection
tdf.Fields.Append fldAccountName
tdf.Fields.Append fldPhone
tdf.Fields.Append fldAddr1
tdf.Fields.Append fldAddr2
tdf.Fields.Append fldAddr3
tdf.Fields.Append fldAddr4
tdf.Fields.Append fldCity
tdf.Fields.Append fldState
tdf.Fields.Append fldZip

'Append the TableDef to the Database's Tabledefs collection
dbAccess.TableDefs.Append tdf

'Refresh the Tabledefs collection
dbAccess.TableDefs.Refresh
Application.RefreshDatabaseWindow

Set fldAccountName = Nothing
Set fldPhone = Nothing
Set fldAddr1 = Nothing
Set fldAddr2 = Nothing
Set fldAddr3 = Nothing
Set fldAddr4 = Nothing
Set fldCity = Nothing
Set fldState = Nothing
Set fldZip = Nothing

'Does the Excel file have Field Names
'Set to True if there are field names
'Set to False if there are no field names
blnHasFieldNames = True

'strTable = "tblAccount"
strTable = tdf.Name


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, AccountDataName, blnHasFieldNames

 

[Jack Leach]

Would it not be possible to use a standard linked table, from your back end
to front end? Link the tblAccounts to the front end you work from, and run
the transferspreadsheet. That should put the data in the backend database
(through the linked table).

You can clear tblAccounts before importing by usings a delete query:

CurrentDb.Execute "DELETE * FROM tblAccounts"

If the back end database happens to change location for whatever reason,
include some startup code in your front end to refresh the tabledef connect
string.

It seems like you're taking the long way around, unless there's something
I'm not seeing here, you should be able to do this in two lines of code.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dwaine Horton]

The backend database location will change once I get the program finished.
Each user will have the program and the backend database so the location of
the backend is different for each user. Each user is also in a different
office so saving the backend to one location for all to access is not
possible.

 

[Jack Leach]

I still think you would be better off with a linked table. You must have the
path to the backend somewhere, else the way you are trying wouldnt work
either.

In a startup procedure for the front end, you can use code such as the
follow to relink the table to the new backend.


CurrentDb.Tabledefs("tblAccounts").Connect = ";DATABASE=C:\NewPath.mdb"
CurrentDb.Tabledefs("tblAccounts").RefreshLink

This should sufficiently refresh the link to tblAccounts.

Dev Ashish provides a module to do this automatically for all tables,
requesting a new path from the user if the current Connect is not found:

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

It is common practice (for me anyway) to verify all table connections on
startup, and relink them if required.

Unfortunately I can't help with your original question, other than relinking
the table. I can't imagine what other types of issues may arise from trying
to "force" a linked table like this.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Douglas J. Steele]

Remember that all you're passing to DoCmd.TransferSpreadsheet is the name of
a table. Access has no way of knowing that you intend that table to be in
another database.

What you can do is instantiate another instance of Access, point that
instance to the back-end database, and use it in conjunction with the DoCmd
command:

Dim appOther As Access.Application

Set appOther= New Access.Application
appOther.OpenCurrentDatabase AccessDataName

appOther.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, AccountDataName, blnHasFieldNames

appOther.CloseCurrentDatabase
Set appOther = Nothing

 

[Dwaine Horton]

Ok, I used this and tested it and it worked for me as long as the linked
access file is in the path that I used to create the link tables. If I move
the file to a different location I get a run time error 3024.

CurrentDb.TableDefs("County Districts 9-26-08").Connect = ";DATABASE=" &
AccessDataName
CurrentDb.TableDefs("County Districts 9-26-08").RefreshLink

AccessDataName is being supplied by a file dialog in which the user selects
the database that has the linked tables.

Example I have MyAccess.db in C:\My Documents\Access Projects. I then move
this file to C:\My Documents and then I get the run time error. When I goto
Debug the line that failed is the refreshlink line.

 

[Dwaine Horton]

the transfer spreadsheet works and not having a problem with it. I need to
refresh the links to that table as I need to perform a query against an
account table that get's created and this table.

Like Doug said, transferspreadhseet only expects the name of the table. It
doesn't matter where that table is - it can be in the DB with your code or in
a linked table. It wouldn't matter if every customer had the database in a
different location - it should only care that a table with that table name
exists. I'm guessing maybe there is something wrong with the
transferspreadhseet command you're issuing. Why don't you post the code that
has the transferspreadhseet command and any other code associated with it.

Also, why are you refreshing the link for that particular table? Is that
really necessary?

Ok, I used this and tested it and it worked for me as long as the linked
access file is in the path that I used to create the link tables. If I move
the file to a different location I get a run time error 3024.

CurrentDb.TableDefs("County Districts 9-26-08").Connect = ";DATABASE=" &
AccessDataName
CurrentDb.TableDefs("County Districts 9-26-08").RefreshLink

AccessDataName is being supplied by a file dialog in which the user selects
the database that has the linked tables.

Example I have MyAccess.db in C:\My Documents\Access Projects. I then move
this file to C:\My Documents and then I get the run time error. When I goto
Debug the line that failed is the refreshlink line.

I still think you would be better off with a linked table. You must have the
path to the backend somewhere, else the way you are trying wouldnt work

[quoted text clipped - 137 lines]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, AccountDataName, blnHasFieldNames