TransferDatabase

[Rob.Mills]

In the frontend I am trying to copy a table and it's data from the backend to another database.

But when I use TransferDatabase all it does is copy the link into the new db. I don't want a link, but an actual copy of the table.

 

[Mile-O]

Import it then.

 

[Rob.Mills]

I'm trying to set this up through code. There's got to be something I can tweak.

 

[Dgar007]

I do this all the time using TransferDatabase. This will write over the entire table when you do the transfer. Do you have it set up like this?

Dim strpath As String
strpath = "h:\Corporate\applicationrepository\sears.mdb"
'transfers all tables from this database to ApplicationRepository
DoCmd.TransferDatabase acExport, "Microsoft Access", path, acTable, "sourcetable", "destinationtable", False

 

[tacieslik]

I saw you code Dgar007, and it will be able to help me too if I can just overcome this small problem:

My Code:
===================
Sub TransferBLOCKStbl()

Dim strpath As String

strpath = "c:\projects\j1427\access\SSCSMirrorApp.mdb"
'transfers all tables from this database to ApplicationRepository
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\projects\j1427\access\SSCSMirrorDataBlk.mdb", acTable, "BLOCKS", "BLOCKS", False

End Sub
===================

My source table is actually a linked table to a Paradox table. When I run this code, it creates the link in my destination database. I don't want that to be the case. I want a 'normal' table to be created but with the data from the source table.

Can this code be modified to handle this?

Thanks for your time.

 

[Dgar007]

Are you saying that you are trying to transferDatabase from another Access db with a link to a PARADOX file? If this is the case, you must do the transferdatabase from the source file (PARADOX). I'm not sure that Access can handle it, I don't use PARADOX at all, you may want to change the path to the PARADOX db and then change the docmd to this:

DoCmd.TransferDatabase ACIMPORT, "PARADOX", "c:\projects\j1427\access\SSCSMirrorDataBlk.mdb", acTable, "BLOCKS", "BLOCKS", False

However, I'm thinking that Mile-O-Phile is right just import.
You could do in code also:

DoCmd.TransferText acImportDelim, "Create an import specification here", "Your Table Name", "Full Path Here", False/True (depending if the file has field names), ""

 

[tacieslik]

Thanks for the reply.

I have an external Paradox table. There is a link to it in my access DB called 'Mirror'. All the Mirror DB needs to do is some how create a table from the linked table. A make table query is no good as it wants to lock the Paradox table even though I'm just reading. So I came across you code and it works a treat except it creates the link, not a normal table?

Importing from Paradox gives me an 'Unsupported' message. I wasn't sure about your second suggestion?

Quote:
DoCmd.TransferText acImportDelim, "Create an import specification here", "Your Table Name", "Full Path Here", False/True (depending if the file has field names), ""

CAn you explain what this will do for me? Sorry, but I'm quite new to VB code.
Thanks in advance.

 

[Dgar007]

As I said, I wasn't sure that Access could handle the transfer from PARADOX. The transfer text won't work either, because it is looking for a file not a table...sorry about the bad advice there.

Is there a specific reason why you don't want to link to the table? Can you export a file from PARADOX to a comma delimited file or excel? If so, then we can do the transfer text. I know this is probably more steps than you would like to do but w/o being a PARADOX user, I'm kinda at a loss.