Copy Table from different DB

[imfarhan]

Hi All,

I have a control datbase , and I would like to copy/paste the table using the VBA code.
Meaniang
copy the X table from "CoreDB"(SourceDB) into "TargetDB".


I'm trying to used the following VBA code but getting some errors

Option Compare Database
Option Explicit
Sub CopyTbl()
Dim conn As ADODB.Connection
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim WSP As DAO.Workspace
Dim rs As ADODB.Recordset

'Set conn = Server.CreateObject("ADODB.Connection")

Set dbs = WSP.OpenDatabase("N:\InfoCom\Farhan\Test_DB\Test1.mdb")

End Sub


" Run-time error 91
Object variable or With block variable not set.

When I google it says I haven't attached the libraries under Reference menu or may not use thw "with2 and "end". don't konw which library or how to use

Yesterday , I was trying to use the CopyObject as mentioned below but didn't get the success.

DoCmd.CopyObject "N:\InfoCom\Farhan\Test_DB\test1.mdb", "emp", acTable, "tablename"

I would appreciate if you could guide me
Many thanks in advance

Farhan

 

[JohnLee]

Hi,

This is the code I use to move a table from one database to another:

============
BEGIN CODE
============

'------------------------------------------------------------
' TransferTable
'
'------------------------------------------------------------
Function TransferTable()
On Error GoTo mcrTransferTable_Err
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\JohnLee\John Lee Backup Information\Questionnaires.mdb", acTable, "tblPCKaroakeListings", "tblPCKaroakeListings", False

TransferTable_Exit:
Exit Function
TransferTable_Err:
MsgBox Error$
Resume TransferTable_Exit
End Function

==============
END CODE
==============

To copy a table from another database use this:

=================
BEGIN CODE
=================

'------------------------------------------------------------
' ImportTable
'
'------------------------------------------------------------
Function ImportTable()
On Error GoTo mcrTestImportTable_Err

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\JohnLee\John Lee Backup Information\db1.mdb", acTable, "tblPCKaroakeListings", "tblPCKaroakeListingsCopy", False

ImportTable_Exit:
Exit Function
ImportTable_Err:
MsgBox Error$
Resume ImportTable_Exit
End Function

===============
END CODE
===============

I Hope this helps

John

 

[imfarhan]

Thanks , but how can I write the Target database, as this VBA code , I'm writing on control datbase where I don't want to keep any table.

I think in your syntax you're copying into the current db but could we define the target databse which differnt than I'm writing this code

I hope it make sense to you
Thanks again
Farhan

 

[JohnLee]

Hi,

Use the copy code that I have just added, change it to suit your needs

John

 

[imfarhan]

Thanks its worked perfectly fine , when I copied into current db(Where function is) but I would like to paste into defferent db

DoCmd.TransferDatabase acImport, "Microsoft Access", "N:\InfoCom\Farhan\Test_DB\Test1.mdb", acTable, "emp", "emp", False

DoCmd.TransferDatabase acImport, "Microsoft Access", "N:\InfoCom\Farhan\Test_DB\Test1.mdb", acTable, "emp", "N:\InfoCom\Farhan\Test_DB\R&D(Testing).mdb", False


so the above in red font code not working , instead of target table I used with the path but got the following error
Run-Time error '2006'
Object Name "....." you entered doesn't follow Microsoft Office Access object name rules

Thanks
Farhan

 

[JohnLee]

Good day,

This is the best solution I can come up with, there may others who may have a better approach. I have tested this and it works fine:

[COLOR=darkgreen]'Import the tblPCKaroakeListings table from the Target database db1.mdb to the control database Questionnaires.mdb
[/COLOR]    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\JohnLee\John Lee Backup Information\db1.mdb", acTable, "tblPCKaroakeListings", "tblPCKaroakeListingsCopy", [COLOR=blue]False[/COLOR]
    [COLOR=darkgreen]'Export the tblPCKaroakelisting table from the Control database Questionnaires.mdb
[/COLOR]    DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\JohnLee\John Lee Backup Information\db2.mdb", acTable, "tblPCKaroakeListingsCopy", "tblPCKaroakeListingsCopy", [COLOR=blue]False[/COLOR]
    [COLOR=darkgreen]'Delete the tblPCKaroakeListingsCopy table from the Control database Questionnaires.mdb
[/COLOR]    DoCmd.DeleteObject acTable, "tblPCKaroakeListingsCopy"

I have assumed that you will always know specifically which table(s) you want to move from one database to another. However I don't understand the need to constantly move a table from one database to another, sounds a bit strange to me, but hey it's your process.

Hope this is of help

John

 

[JohnLee]

Hi,

As an after thought, you may want to also delete the table that you have copied from the target database to the destionation database,

That is, in the code I provided it still leaves the target table in the original database, so to complete the process, I guess yo will want to be sure that it no longer exists in the database that your table originally came from and so you will need to have this at the end of the code I provided:

Dim db As DAO.Database [COLOR=darkgreen]' Declare db as the DAO Database[/COLOR]
 
    [COLOR=darkgreen]'Set db to open the target database[/COLOR]
    Set db = OpenDatabase("C:\JohnLee\John Lee Backup Information\db1.mdb")
    [COLOR=darkgreen]'Delete the target table in the target database[/COLOR]
    db.TableDefs.Delete ("tblPCKaroakeListings")

This ensure that your table it truely moved [although it has been a series of import and export from your target database to your control database to your detination database.

So you table now only exists in your destination database and not in either your target or control database.

I know it's long winded, but it does work as I have tested this out.

Hope this helps you.

John

 

[imfarhan]

Hi John
Many thanks for your help , I ve tried your code and its work pefectly well on my testing db. I will make it alive in my live sysem


Thanks again for your time and help

Regards
Farhan

 

[JohnLee]

Hi Imfarhan,

Glad to have been of assistance, this is a great forum, I learnt alot from the various experts on here and will no doubt learn more in the future.

John