Append data from a table in other MDB

[shyamsundar]

Hi,

I am able to use docmd.transferdatabase,,,,,,,etc.. to import tables from other database, but I need to append the data to the existing one with some criteria.

Any help please???
Shyam
cwizshyam@yahoo.com

 

[jebry]

Hi!

You could link to the table and then run queries against it just like it was in your own Db. Alternatively, you could make it a two step process and import the table into a temp table and then run an append query to get the correct records into the existing table.

hth

Jeff Bridgham
bridgham@purdue.edu

 

[shyamsundar]

Hi Jeff,

Thanx for the tip.

But my problem is little lengthier.

I have different MDB received from different parts of the country, and as per the user's selection the MDB file is selected and the data is appended, and moreover I do have around 20 tables to append from each MDB which automatically checks for the updates.

u r expert suggestion pl. Shyam
cwizshyam@yahoo.com

 

[jebry]

Hi!

To my knowledge you cannot tell the TransferDatabase method (or action) to append to an existing table. If you give the method the name of an existing table it will alter that name and import into a new table. So I would recommend either linking, which is an option for the TransferDatabase method, or importing into a temporary table (all 20 tables). Then run append and/or update queries to get the information in. That said, you may to able to make the task somewhat easier by opening the MDB in code and looping through the TableDefs collection and using the Name property in the TransferDatabase method. You also use some variation of the Name property (or the property itself assuming that a table with that name does not already exist) for the table to transfer into. You can also have your append and update queries already set and ready to go so you can run them from this code also. Of course, you will need to delete the temporary tables either just after the queries run or just before you pull in the new tables.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[shyamsundar]

Thanx jeff, i will try this method, and let u know.

thanx a lot
Shyam
cwizshyam@yahoo.com

 

[shyamsundar]

Hi,

Thanx very much, who have helped me for the solution of this thread. I would like to share this with u all, which follows.......

Private Sub Command0_Click()
Dim strPATH As String
Dim strMDB As String
Dim strTBL As String
Dim qryAPP As String
strPATH = Left$(CurrentDb.Name, InStr(CurrentDb.Name, Dir(CurrentDb.Name, vbDirectory)) - 1)
strMDB = strPATH & lstCOUNTRY.Value 'this value is selected from list object
'MsgBox strMDB
Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Codesheet&quot'table name to import
rs.MoveFirst
While Not rs.EOF()
strTBL = rs.Fields(0).Value
qryAPP = "insert into " & strTBL & " select * from " & strTBL
qryAPP = qryAPP & " in " & strMDB
'DoCmd.SetWarnings False
MsgBox qryAPP
DoCmd.RunSQL qryAPP
rs.MoveNext
Wend
End Sub

I am further developing to have to table and store the book mark after importing, which will help later to start from that point to import/refresh again. I will share the same very soon

Thanx very much once again.

Shyam Shyam
cwizshyam@yahoo.com