data between 2 databases

Results 1 to 2 of 2

Thread: data between 2 databases

  1. #1
    Join Date
    Dec 1969

    Default data between 2 databases

    posted earlier regarding this one - have got a bit further - thanks oly!<BR>basically though back to the drawing board<BR>Ive got 2 databases with identical tables (1 sql server and 1 access ) the sql server one is the &#039;live one&#039; which gets updated every now and then they want a snapshot in access so the obvious thing to do is connect to the sql server db and connect to the access db and loop through the tables - inserting all the records from the sql server tbl to the access one - the point i am stuck at is the sql statement that will insert into the access db <BR><BR><BR>anyway heres the code ... <BR><BR><BR><BR>Dim adoConn As ADODB.Connection<BR>Dim accessConn As ADODB.Connection<BR><BR>Dim RS As ADODB.Recordset<BR>Dim rsAccess As ADODB.Recordset<BR><BR>Dim strCaption As String<BR>Dim SN As String<BR>Dim I As Single<BR>Dim Recs As Integer<BR>Dim Counter As Integer<BR>Dim BarString As String<BR>Dim MdbFile As String<BR>Dim Junk As String<BR>Dim strAdoConn As String<BR>Dim strconn<BR>Dim strsql<BR><BR> Set adoConn = New ADODB.Connection<BR> strconn = "DRIVER={SQL Server};SERVER=;UID=sa;PWD=877;DATABAS E=mydb" &#039;App.Path & "Examples.mdb"<BR> Set adoConn = New ADODB.Connection<BR> <BR> adoConn.Open strconn<BR> <BR> strconn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Application.CurrentProject.Path & "
    ead2000.mdb"<BR> Set accessConn = New ADODB.Connection<BR> accessConn.Open strconn<BR> <BR> &#039; now we have a recordset containing the names of all the tables and queries in the database<BR> Set RS = adoConn.OpenSchema(adSchemaTables)<BR> &#039;Now we loop through the recordset, row-by-row until we reach the End Of File<BR> Do Until RS.EOF<BR> &#039; make sure we&#039;re using the names of Tables that aren&#039;t<BR> &#039; System Object Tables, or tables that start with USys, or "Views" (queries)<BR> If RS.Fields("TABLE_TYPE") = "TABLE" Then<BR> &#039; populate the List Box<BR> If InStr(RS.Fields("TABLE_NAME"), "sys") &#062; 0 Then<BR> &#039; skip system tables<BR> <BR> Else<BR> MsgBox RS.Fields("TABLE_NAME")<BR> strsql = "delete * from " & RS.Fields("TABLE_NAME")<BR>&#039;flush the asp <BR> accessConn.Execute strsql<BR>*****************<BR>this is the statement where i need to insert into the access from the sql server table<BR><BR> strsql = "Insert into " & RS.Fields("TABLE_NAME") & " select * from " & adoConn.Execute<BR> End If<BR> End If<BR> <BR> RS.MoveNext<BR> Loop<BR> &#039; close objects when we&#039;re done and set to Nothing.<BR> If Not (RS Is Nothing) Then<BR> RS.Close<BR> Set RS = Nothing<BR> End If<BR><BR>any help would be much appreciated - there must be an easy way! <BR><BR>thanks<BR>

  2. #2
    Join Date
    Dec 1969

    Default What's the error <EOP>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts