Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Orlando Florida
    Posts
    20

    Smile Unanswered: Insert data from remote database

    Hi All,

    I am trying to insert data from a remote database to local database. I found some code on this site, but; I cannot get it to work. Code follows:

    Private Sub insertNewRec()
    ' This subroutine is used to insert accounting data
    ' into local database from remote database.

    On Error GoTo Err_insertNewRec
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cn2 As New ADODB.Connection
    Dim sSQL As String
    Dim sDB As String

    ' Set the string to the path of your Irp database
    sDB = "c:\irp-acct-Server23-Dev.mdb"
    ' Open connection to the database
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;" & _
    "Data Source=" & sDB & ";"

    'Open the connection to remote database
    With cn
    .Open "DSN=Irp-DSN;"
    End With

    rs.Open "select job,title,images from tbl_acctlog where (job>6323 and uname='bdavis')"

    Do While Not rs.EOF
    cn2.Execute "insert into tbl_acctlog (job,title,images) select " & rs.Fields("job") & ",'" & rs.Fields("title") & ",'" & rs.Fields("images")
    rs.MoveNext
    Loop

    'Close object
    If rs.State <> adStateClosed Then rs.Close
    Set rs = Nothing
    If cn.State <> adStateClosed Then cn.Close
    Set cn = Nothing

    If cn2.State <> adStateClosed Then cn2.Close
    Set cn2 = Nothing

    Exit_insertNewRec:
    Exit Sub

    Err_insertNewRec:
    MsgBox Err.Description & ": " & Err.Description
    Resume Exit_insertNewRec

    End Sub

    The error I get when I execute this code is: "The connection cannot be used to perform this operation. It is either closed or invalid in this context.: The connection cannot be used to perform this operation. It is either closed or invalid in this context."

    Any help would be greatly appreciated.

    Thanks for your time and consideration.

    B. Davis-

  2. #2
    Join Date
    Nov 2003
    Location
    Orlando Florida
    Posts
    20
    Thanks everyone. I found out what my problem was. I did not have a connection on the select statement.

    Thank you for your time.

    B. Davis-

Posting Permissions

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