TransferDatabase

[dorourke]

Hi

I am running front end databased (A) linked to a backend database (B)

I need to issue a command to import a database table into the backend database (B) whilst in the front end (A)

I assume the use of the transferdatabase command, but how do i get it to import the data into a different database?

Thanks

 

[psemianonymous]

You'd have to open up a new Access window "Access.Application" object, and then open the backend file directly via that new window, and then run the TransferDatabase command from inside that object.

There are ways to run SQL on remote Access tables, but it's limited in scope of what it can do.

 

[LarryDeLaruelle]

Here is the code I use to do the transfer:

Private Sub cmdUpdate_Click()
Dim dbTemp As DAO.Database
Dim rsTemp As DAO.Recordset
Dim intLoop As Integer


Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset("tblTableNames")
rsTemp.MoveLast
rsTemp.MoveFirst

For intLoop = 1 To rsTemp.RecordCount
DoCmd.DeleteObject acTable, rsTemp!tablename
DoCmd.TransferDatabase acImport, "Microsoft Access", "G:\database\TCC_Contacts\Mailing_Data.mdb", acTable, rsTemp!tablename, rsTemp!tablename
rsTemp.MoveNext
Next intLoop
rsTemp.Close

Set rsTemp = dbTemp.OpenRecordset("tblLastUpdate")
rsTemp.MoveFirst
rsTemp.AddNew
rsTemp!mytime = Now()
rsTemp.Update
rsTemp.MoveLast
lblUpdate.Caption = rsTemp!mytime
rsTemp.Close
Set dbTemp = Nothing

End Sub

I'm using A2K with the DAO 3.6 reference enabled.

tblTableNames contains a list of the tables that are to be imported. If you are only importing one table this would not be necessary nor would the loop to step through the list of tables.

Note that I delete the original table before importing the new one. This has to be done since Access would add a numeric suffix to the table name if a table of the same name as the one being imported already exists.

You'll need to change the string "G:\database\TCC_Contacts\Mailing_Data.mdb" to the path where you db/table reside.

The last part of the code merely writes a record of the last update. This is informational only for the user and not a necessary part of the process.

Let me know if you have any additional questions or encounter any problems.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org