Pick Data From One MDB To Another
[152 byte] By [AskProf] at [2007-11-19 19:52:16] 
I need to pick data from one MDB database table and insert into another MDB database table. If it can be finished by 1 SQL and how? Please help. Thanks!
# 1 Re: Pick Data From One MDB To Another
Here's how I add a record from a flexgrid to a table:Sub InsertIntoD****C****()
Dim strSQL As String
strSQL = "INSERT INTO D****C**** " ' changed the name, left off fields
strSQL = strSQL & "(AccountNo,LastName,FirstName,AccountType)"
strSQL = strSQL & "VALUES ("
With frmC.flx
strSQL = strSQL & .TextMatrix(mrow, 0) & ","
strSQL = strSQL & "'" & .TextMatrix(mrow, 1) & "',"
strSQL = strSQL & "'" & .TextMatrix(mrow, 2) & "',"
strSQL = strSQL & "'" & .TextMatrix(mrow, 3) & "',"
' another 15 fields are added in this manner
End With
' Debug.Print strSQL
cn.Open GetConnString ' This sub is my connection string
cn.Execute strSQL
Set cn = Nothing ' set it to nothing when finished
End Sub
dglienna at 2007-11-9 20:11:10 >
# 2 Re: Pick Data From One MDB To Another
Hello, dglienna:Thank you very much! It works well.
But I still have questions.
Suppose we have 2 MDB file:
dbFrom.MDB -> table tblFrom
dbTo.MDB -> table tblTo
I want to copy all records from tblFrom of dbFrom.MDB into tblTo of dbTo.MDB. It's TWO MDB files and NOT WITHIN ONE DATABASE.
Can we pick data directly from one MDB database table and insert into another MDB database table? That means if we can perform this by SQL only without loading data to a grid and then inserting to another table?
AskProf at 2007-11-9 20:12:10 >
# 3 Re: Pick Data From One MDB To Another
Yes it can be done, but the table must already exist on both Databases. The Table fields also must be defined exactly the same on each database
(That is for my example to work)
This saves hours of coding ! Enjoy !
OPTION EXPLICIT
Public DBExport As Database
Public DBImport As Database
Public RSExport As Recordset
Public RSImport As Recordset
Public Sub Copy_Table()
Dim IncomingPath As String
Dim CurrentPath As String
Dim StrSql As String
On Error GoTo E1
IncomingPath = (DBPathToCopyFrom & "\DB1.mdb")
CurrentPath = (DBPathToCopyTo & "\DB1.mdb")
Set DBImport = OpenDatabase(IncomingPath)
Set DBExport = OpenDatabase(CurrentPath)
StrSql = "Delete * from [MyTableName]" 'delete all records first
DBExport.Execute StrSql
StrSql = "INSERT INTO [MyTableName] SELECT * FROM [" & IncomingPath & "].[MyTableName]"
DBExport.Execute StrSql
DBImport.Close
DBExport.Close
SET DBImport = Nothing
SET DBExport = Nothing
Exit Sub
E1:
MSGBOX "ERROR COPYING TABLE"
Exit Sub
George1111 at 2007-11-9 20:13:14 >
# 4 Re: Pick Data From One MDB To Another
Good one George :thumb:Just to add, if you want to be more specific you can mention the field names in your select and insert queries just to be on safer side.
Shuja Ali at 2007-11-9 20:14:13 >
# 5 Re: Pick Data From One MDB To Another
Hello, George:Great! It's what I want and quite concise. Thank you very much!
AskProf at 2007-11-9 20:15:12 >
# 6 Re: Pick Data From One MDB To Another
Above method works normally. But I have new question:If I have password protection in incoming database. How should I write the SQL? I have tried the following or silimiar but failed:
"INSERT INTO [MyTableName] SELECT * FROM [Jet OLEDB:Database Password=test; " & IncomingPath & "].[MyTableName]"
AskProf at 2007-11-9 20:16:17 >
# 7 Re: Pick Data From One MDB To Another
The password belongs in the conection string, not the query. Check out www.connectionstrings.com for specifics on password connections.dglienna at 2007-11-9 20:17:21 >
# 8 Re: Pick Data From One MDB To Another
Is there any method to share data between 2 password-protectiing database files by SQL only in such case?AskProf at 2007-11-9 20:18:23 >
# 9 Re: Pick Data From One MDB To Another
I found a similar way for exporting data from Access to SQL server:Run in SQL Server:
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password='', table Name)
I think there should be similar for my case. Anyone would help?
AskProf at 2007-11-9 20:19:20 >
