[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1015
  • Last Modified:

ADODB Connection to mdb on remote server

Experts,  I would like to enable a function in my Microsoft Access 2003 Database where records from the Local DB Table (PROBLEMLOG) are written to an Access mdb containing the exact same table on a remote http server.

The mdb is accessible at a URL similar to: http://www.myurl.com.au/mydblocation/mydbname.mdb

I'm new to ADODB connection strings and am having trouble working it out.  Please have a look at my code snippet at tell me what I am doing wrong.  

Thanks

Public Function uploadLog()
'Uploads the Problem to the Server based mdb

Dim cn As ADODB.Connection
Dim rsLog As DAO.Recordset
Dim rsWeb As ADODB.Recordset
Dim strSQL As String
Dim strCn As String
Dim myURL As String
Dim fld as field

'Get the local records to be send to the server DB
strSQL = "SELECT * FROM PROBLEMLOG WHERE SendToSupport=False"
Set rsLog = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rsLog.RecordCount > 0 Then  
'There are some records to send
'Now Make connection to the server side Access 2003 mdb
'***** TROUBLE HERE!  *********    
    Set cn = New ADODB.Connection
    myURL = "//www.myurl.com.au/mydblocation/mydbname.mdb"
    strCn = "Provider=MS Remote; Remote Server=" & myURL _
    & "Remote Provider=Microsoft.Jet.OLEDB.4.0;"
    With cn
        .ConnectionString = strCn
        .CursorLocation = adUseServer
        .OPEN
    End With

'***** I THINK ITS OK FROM HERE ON? ********
'***** BUT CAN'T GET PAST THE CONNECTION BIT TO SEE *****

    'Open the Server Side Recordset for editing
    Set rsWeb = New ADODB.Recordset
    rsWeb.OPEN "SELECT * FROM PROBLEMLOG", cn
    
    'If connection made then continue to write
    rsLog.MoveFirst
    Do Until rsLog.EOF
        rsWeb.AddNew
        For Each fld In rsLog.Fields
            Debug.Print fld.NAME, rsLog(fld.NAME), fld.Type
            rsWeb(fld.NAME) = rsLog(fld.NAME)
        Next fld
        rsWeb.Update

'Edit the local record so that it it does get resend next time
        rsLog.Edit
        rsLog!SentToSupport = True
        rsLog.Update
'move onto the next record 
       rsLog.MoveNext
    Loop
End If
End Function

Open in new window

0
Carl Sudholz
Asked:
Carl Sudholz
  • 2
  • 2
1 Solution
 
hesCommented:
Sorry cap lock stuck
0
 
Carl SudholzManaging DirectorAuthor Commented:
Provide the link to a solution that led me down the right track to solving the problem.

Thanks
0
 
Carl SudholzManaging DirectorAuthor Commented:
FYI,  Yep no good having an MDB on the server.  Solution was to:
1. set up a Database on mysql, with tables and user permissions
2. Install the MySQL connector driver (mysql-connector-odbc-5.1.8-win32.msi) from http://dev.mysql.com/downloads/connector/odbc/5.1.html
3. Set the connection string to:

Private Const conString As String = "Provider=MSDASQL;" & _
                                    "DRIVER={MySQL ODBC 5.1 Driver};" & _
                                    "SERVER=127.0.0.1;" & _
                                    "DATABASE=mydbname.mdb;" & _
                                    "UID=systemuser;" & _
                                    "PASSWORD=password1;" & _
                                    "OPTION=3"

From there the function works a treat pretty much as coded in the question.

Cheers
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now