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.
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;"
.ConnectionString = strCn
.CursorLocation = adUseServer
'***** 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
Do Until rsLog.EOF
For Each fld In rsLog.Fields
Debug.Print fld.NAME, rsLog(fld.NAME), fld.Type
rsWeb(fld.NAME) = rsLog(fld.NAME)
'Edit the local record so that it it does get resend next time
rsLog!SentToSupport = True
'move onto the next record