TransferDatabase method with linked table

[chathag]

I have an odbc linked table in database one. Using the transferdatabase method I want to copy the table into database two as an embedded table (not linked). I have tried to do this but an odbc linked table appears in database two.

Database two will be sent to users who are not based on the network. I could create the table structure in database two and use an insert query to populate the data from database one, but I would prefer to use the transferdatabse method.

C

 

[boblarson]

The only way I can think of that you would get the table across without the link is to do a make table query in your initial database to create a new table and then you can use TransferDatabase to move that new table across.

 

[chathag]

I used the append query to create table in destination database! doh!!

Thanks

GC

 

[NigelShaw]

Hi

I had the very same problem just a few weeks ago. I'll post what I did but I think I used a Sql statement.

I'll be back ( and no, not in a dodgy terminator accent either )

nigel

 

[boblarson]

( and no, not in a dodgy terminator accent either )

Too late - you should have warned us up front. The minute I saw it, that's what I thought of

 

[NigelShaw]

Ha ha

suppose the dark shades didn't help either.......

 

[NigelShaw]

Im back

here is what i did

Public Sub SendTablesFromExternalDB()

[COLOR="SeaGreen"]'transfer a table from a linked table to another database[/COLOR]

Dim appAccess As Access.Application
Dim strDatabaseName As String

[COLOR="SeaGreen"]'Get the path of the database you want the table from[/COLOR]
strConnectDBPath = Application.CurrentProject.Path & "\ExternalDb.accdb"

[COLOR="SeaGreen"]'Get the path of the table you want the table to be inserted[/COLOR]
[COLOR="SeaGreen"]'This is based on a textbox with the path as a string. i used a windows browse box to get the path[/COLOR]
strDatabaseName = Me!txtDBtoUse.Value


            DoCmd.SetWarnings False

            strSQL = "SELECT * INTO tblOptions IN '" & strDatabaseName & "' FROM tblOptions;"
            DoCmd.RunSQL strSQL

         
            DoCmd.SetWarnings True

End Sub

regs

Nigel