Combinbing two pieces of code....

[Fherrera]

Hi, i'm trying to combine some tables, but they aren't in the same database... Trying to do this all automatically as well

So anyway, I found (in tek-tips /used some code to update different access databases and I also have some code to combine tables using CurrentDB. They are below:

Public Sub UpdateTables(sdbFileName as string, sdbTableName)
' open up database and execute update query
'
	Dim cn as New ADODB.Connection

	cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=D:\" & sdbFileName & ".mdb"

	'To update a record run this
	msgbox "UPDATE " & sdbTableName & " SET " & sdbTableName & ".[DATE] = Str(Cdate([DATE]))"

'	cn.execute "UPDATE " & sdbTableName & " SET " & sdbTableName & ".[DATE] = Str(Cdate([DATE]))"
	cn.close

End Sub

Function MergeMe()
    'Clear out main table first
    With CurrentDb
        .Execute ("DELETE * FROM Test;")
    'Then do some insertion
        .Execute ("INSERT INTO Test SELECT TAG,DATE,VALUE FROM Table1;")
        .Execute ("INSERT INTO Test SELECT TAG,DATE,VALUE FROM Table2;")
    End With
End Function

Obviously I have absolutely no experience with VBA and databases (although I do have a bit on VBA with Excel stuff) So, my question is how can I run the INSERT INTO statements to go to a different table... For example, if I run this from an access database with an empty table (structure is all set up) and I want it to combine two tables from two different (other) access files... (this is due to size restrictions on Access 97)

Any suggerstions?

Thanks

Frank

 

[PHV]

Why not simply using linked tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244