Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    I'm trying to get a faster method for Downloading a web based "SQL server" tables data to a local table for backup, currently i'm using:

    Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "Driver={SQL Server};" & _
    "Server=www.somewhere.com;" & _
    "Address=???.???.???.???,1433;" & _
    "Network=DBMSSOCN;" & _
    "Database=????????;" & _
    "Uid=????????;" & _
    "Pwd=??????;"
    End Sub

    To connect then


    SQL1 = "Select * from Remote_Table"
    rs123.Open SQL1, oConn
    Set rs22 = CurrentDb.OpenRecordset("Local_Table")
    rs123.MoveFirst
    Do While Not rs123.EOF
    DoEvents
    rs22.AddNew
    rs22![Field1] = rs123![Field1]
    rs22![field2] = rs123![field2]
    etc

    rs22.Update
    rs123.MoveNext
    Loop

    This is very slow. In a perfect scenario I just want to Append the [Remote_table] to a preformated blank [Local_table], any suggestions???

    At the moment it can take an Hour to Download.

    With thanks in Antici Graliv

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After you connect, what happens if you change your sql string to a simple append query and use docmd.runsql rather than looping through the recordset? Since it is an append query, if you don't want the warnings that go with append queries you can add docmd.SetWarnings False before and True after to kill the warnings.

    SQL1 = "INSERT INTO Local_Table " & _
    "SELECT Remote_Table.* " & _
    "FROM Remote_Table;"

    docmd.SetWarnings False
    docmd.runsql
    docmd.SetWarnings True

    Peter

  3. #3
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,188
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Alternatively set up a maintenance task on the SQL server to backup to disk and use a remote share as the destination.

    cheers, Paul

  4. #4
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by PeterN View Post
    After you connect, what happens if you change your sql string to a simple append query and use docmd.runsql rather than looping through the recordset? Since it is an append query, if you don't want the warnings that go with append queries you can add docmd.SetWarnings False before and True after to kill the warnings.

    SQL1 = "INSERT INTO Local_Table " & _
    "SELECT Remote_Table.* " & _
    "FROM Remote_Table;"

    docmd.SetWarnings False
    docmd.runsql
    docmd.SetWarnings True
    Apologies for the delay in reply only just got back on this project

    Peter I tried that and it searches for the [Remote_Table] Table in the current Database and gives an error message to that effect.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought about that shortly after I posted. I always work with DAO and have never been comfortable with ADO. My guess is that it is something to do with establishing the remote connection but I'm out of my depth at that point. In your original code, it just seemed to me a little inefficient to loop through the recordset rather than just run a query. Not sure how running SQL fits into the picture. As Paul suggests, maybe this is better run on the SQL Server end. One final thought and again, this may not be possible given the web-based setup: Can you link your Access database to the table in question in SQL Server? Then I think Access would see it as local. Just a thought. Over to people who know better

Posting Permissions

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