Carl Sudholz
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry cap lock stuck
ASKER
Provide the link to a solution that led me down the right track to solving the problem.
Thanks
Thanks
ASKER
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.ms i) 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
1. set up a Database on mysql, with tables and user permissions
2. Install the MySQL connector driver (mysql-connector-odbc-5.1.
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