Querying 2 different databases.
I have 2 MS-Access databases, one that is 180 megs and grows weekly, includes all data for the entire South Area. The other one I want to bring in the records from the 1st DB that meet a certain requirement such as MU = 650 - 675.. This would greatly reduce the overall size of the table and allow quicker reporting. The question is how do I write the code to query one database and insert that data into the other database with VB?
Create 2 connections. Select a recordset from the first using your criteria disconnect the recordset, then connect it to the table in the second database.
Leon
Leon
Add a reference to your project (Project -> References) 'Microsoft ActiveX Data Objects 2.? Library'. Then you can connect do db's using ADO
Private db1 As New ADODB.Connection
Private db2 As New ADODB.Connection
Private rs1 as New ADODB.Recordset
Private rs2 as New ADODB.Recordset
Private db1Path As String
Private db2Path As String
Private sql as string
db1Path = (enter path of db1 in " ")
db2Path = (enter path of db2 in " ")
db1.Open "Provider=Microsoft.Jet.OL
db2.Open "Provider=Microsoft.Jet.OL
sql = "SELECT * FROM tablename WHERE MU>=650 AND MU<=675" 'replace tablename with the name of the table the records are in
rs1.open sql, db1
Do While Not rs1.EOF
sql = "INSERT INTO tablename (field1name, field2name) VALUES (" & _ 'replace tablename with the name of the table the records are in
rs1![Field1] & "," & rs![Field2] & ")" 'if you are inserting data into a number field this is fine, if you inserting
rs2.Open sql, db2 'data into a text field use ' ex. " ' " & rs![Field1] & " ',' " etc.
rs1.MoveNext
Loop
Private db1 As New ADODB.Connection
Private db2 As New ADODB.Connection
Private rs1 as New ADODB.Recordset
Private rs2 as New ADODB.Recordset
Private db1Path As String
Private db2Path As String
Private sql as string
db1Path = (enter path of db1 in " ")
db2Path = (enter path of db2 in " ")
db1.Open "Provider=Microsoft.Jet.OL
db2.Open "Provider=Microsoft.Jet.OL
sql = "SELECT * FROM tablename WHERE MU>=650 AND MU<=675" 'replace tablename with the name of the table the records are in
rs1.open sql, db1
Do While Not rs1.EOF
sql = "INSERT INTO tablename (field1name, field2name) VALUES (" & _ 'replace tablename with the name of the table the records are in
rs1![Field1] & "," & rs![Field2] & ")" 'if you are inserting data into a number field this is fine, if you inserting
rs2.Open sql, db2 'data into a text field use ' ex. " ' " & rs![Field1] & " ',' " etc.
rs1.MoveNext
Loop
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT [source.]field1[, field2[, ...]
FROM tableexpression
S