Append non linked table

[SidCharming]

I need to perform the following:

[ol][li]create link to table z from data db[/li]
[li]remove data from table x[/li]
[li]append data from linked table z to table x[/li]
[li]remove link to table z[/li][/ol]

One database contains all the 'data', I don't want to import the tables because they don't have descriptions and the data db is updated (over written) daily so I can modify the tables to only have them be over-written. I am building a seperate db that is for report building and I don't like to work with live data.

I am finding that I can not append a table into another unless a link exists. I don't want to leave behind a link. My thoughts are to build the link, perform the delete & append then remove the link. The number of tables that this will happen to is around 10.

that is my limited knowledge of this request. any help building the code or direction to a sample?

TYIA (Thank you in advance)


Sid from Minnesota

 

[drctx]

you can use the docmd.transferdatabase method.

DoCmd.TransferDatabase acLink, "Microsoft Access", strFileName & dbName, _
acTable, strTableNameSrc, strTableNameDest

' do what you need to do here ** then delete linked table.

DoCmd.DeleteObject acTable, strTableNameDest

 

[SidCharming]

Will the transferdatabase import the data or bring in the table (with structure and data)?

I worked on this quite a bit yesterday and found this to work for me to bring the data into my detail populated tables after clearing them with SQL "DELETE * FROM tblNames"

Here is the code I used to create my link, then populate my [tblNames]:

Private Sub GetUpdates()

DoCmd.SetWarnings False

' Create link to db1 table tblNames
Call LinkUp

' Remove records from table tblNames
DoCmd.RunSQL "DELETE * FROM tblNames"

' Append records from linked tblNamesLinked to tblNames
DoCmd.RunSQL "INSERT INTO tblNames SELECT tblNames1.* FROM tblNames1;"

' Remove linked table with db1 table tblNames
DoCmd.RunSQL "DROP table tblNames1"
' Refresh database window to see if any linked tables remain from transaction
CurrentProject.Application.RefreshDatabaseWindow

' Return message for completion
MsgBox "Import is Completed.", vbOKOnly

End Sub
------------------
Sub LinkUp()

Dim cat As New ADOX.Catalog
Dim Tbl As New ADOX.Table

' Open the catalog (the current db)
cat.ActiveConnection = CurrentProject.Connection

' Set the name and target catalog for the table
Tbl.Name = "tblNames1" ' Name given to linked table
Set Tbl.ParentCatalog = cat

' Set the properties to create the link
Tbl.Properties("Jet OLEDB:Create Link") = True
Tbl.Properties("Jet OLEDB:Link Datasource") = "e:\db1.mdb"
' Below code not needed unless password protected
'Tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
Tbl.Properties("Jet OLEDB:Remote Table Name") = "tblNames"

' Append the table to the collection
cat.Tables.Append Tbl

' Clear memory (cat)
Set cat = Nothing

End Sub

Do you see any issues I am not seeing for using this code? I have a form setup to use GetUpdates().



Sid from Minnesota