Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: How to move data from SQL Server to MS Access

    Hi,

    I've scripted an MS Access database with tables using ADOX.

    I wonder what the quickest way is to fill these tables from an SQL Server source (not using a DTS).

    Regards,

    Wim Venema

  2. #2
    Join Date
    Mar 2006
    Location
    south jersey, usa
    Posts
    53
    dts is the quickest & easiest way, period



    but you could try modding this file i have. This vbs script updates records from an access db to sql.
    Code:
    dim accessfile, sqlserver, sqluser, sqlpwd, sqldb
    dim aconn, ars, sconn, srs, query
    
    accessfile = "\\sus\c$\tasks\actagt2.mdb"
    sqlserver = "sus\sqlexpress"
    sqluser = "curtis"
    sqlpwd = "suckit"
    sqldb = "Agents"
    
    sub accessconn ' open a connection to access db'
      set aconn = CreateObject("ADODB.Connection") 'prep connection'
      set ars = CreateObject("ADODB.Recordset") ' prep recordset'
      aconn.open "DRIVER={Microsoft Access Driver (*.mdb)};" &_ 
                 "DBQ=" & accessfile & ";DefaultDir=;UID=;PWD=;"
    end sub
    
    sub closeaccess ' close the connection to the db
      ars.close
      set ars = nothing
      aconn.close
      set aconn = nothing
    end sub
    
    sub sqlconn ' open a connection to access db'
      set sconn = CreateObject("ADODB.Connection") 'prep connection'
      set srs = CreateObject("ADODB.Recordset") ' prep recordset'
      sconn.open "PROVIDER=SQLOLEDB;DATA SOURCE=" & sqlserver & ";UID=" & sqluser &_
                 ";PWD=" & sqlpwd & ";DATABASE=" & sqldb
    end sub
    
    sub closesql ' close the connection to the db
      'srs.close
      set srs = nothing
      sconn.close
      set sconn = nothing
    end sub
    
    sub main
    dim codes
    codes = ""
    accessconn
    sqlconn
    query = "select * from agents"
    ars.open query, aconn
    do while not ars.eof
      query = "select code from agents where code = " & ars.fields("code")
      srs.open query, sconn
      if srs.eof then 
        'msgbox "code " & ars.fields("code") & " not found"
        codes = codes & vbcrlf & ars.fields("code")
        srs.close
      else
        query = "update agents set plua = '" & ars.fields("plua") & "', clua = '" & ars.fields("clua") & "' where code = " & ars.fields("code")
        srs.close
        srs.open query, sconn
      end if
      ars.movenext
    loop
    closeaccess
    closesql
    msgbox "done" & vbcrlf & codes
    end sub
    
    main
    "They say Moses split the Red Sea
    I split the blunt and rolled the fat one, I'm deadly"
    -- Tupac 'Blasphemy'

  3. #3
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Thanks for the suggestion, however it is quite slow.
    I agree using a DTS is much faster however all objects are variable (filenames, tablenames etc) and scripting the DTS itself is hard to do.
    In the meantime I found a more quicker method (simplified):

    strAdoConStr1="driver={SQL Server}; Server=(local);UID=user;PWD=pwd;DATABASE=test"
    strAdoConStr2="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=c:\MSDB.mdb"

    cnn1.open strAdoConStr1
    cnn2.open strAdoConStr2

    strQry1 = "SELECT * FROM SQLSERVERTABLE "

    rst1.open strQry1, cnn1, 3,1
    rst2.open "MSDBTABLE", cnn2,1,3,2

    while not rst1.eof
    rst2.addnew
    for i=0 to NumberOfColumns-1
    rst2(i) = rst1(i)
    next
    rst2.update
    rst1.movenext
    wend

    cnn1.close
    cnn2.close
    Last edited by Wim Venema; 09-23-06 at 01:53.

  4. #4
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    I don't recommend this, but the last time I did it (with only three tables), I just exported from SQL to Excel and then imported from Excel to Access. It was a pain, but it did allow me to reformat the date fields so they would transport correctly.

Posting Permissions

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