Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    42

    Question Unanswered: Access SQL Synchronisation

    I have an access front and back end on a laptop. Can I synchronise with SQL server?

  2. #2
    Join Date
    Mar 2006
    Location
    south jersey, usa
    Posts
    53
    here's a vbs i wrote to update sql's plua and clua columns with the data in an access file.

    Code:
    dim accessfile, sqlserver, sqluser, sqlpwd, sqldb
    dim aconn, ars, sconn, srs, query
    
    accessfile = "\\sus\c$\tasks\actagt2.mdb"
    sqlserver = "sus\sqlexpress"
    sqluser = "cs"
    sqlpwd = "xxxxxx"
    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
    Dec 2002
    Posts
    42
    Thanks I'll give it a go

Posting Permissions

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