[AccessD] SQLOLEDB

Can I use an IP address as the data source property for the adodb connection?

As I have discussed, I am hitting the SQL database over the internet using Hamachi.  this morning I 
am researching how to bind forms to an ADO recordset.  The first thing I have to do is establish a 
connection to the database as shown below.

     With cn
         .Provider = "Microsoft.Access.OLEDB.10.0"
         .Properties("Data Provider").Value = "SQLOLEDB"
         '.Properties("Data Source").Value = "5.203.167.79"
         .Properties("Data Source").Value = "VMDev\SQLExpress"
         .Properties("Initial Catalog").Value = cfw.mSVAppData("DbName")
         .Properties("User ID").Value = "XXX"
         .Properties("Password").Value = "YYY"
         .Open
     End With

Using the ip address for the Data Source property throws an error on the .Open.  Simply changing 
that to the hard coded database name allows the .Open.

I changed to the following and it works!

         strCnn = "Data Source=" & cfw.mSVAppData("ServerIP") & ",1433;Network 
Library=DBMSSOCN;Initial Catalog=" & cfw.mSVAppData("DbName") & ";User ID=" & "XXX" & ";Password=" & 
"YYY" & ";"
         With cn
             .ConnectionString = strCnn
             .Provider = "Microsoft.Access.OLEDB.10.0"
             .Properties("Data Provider").Value = "SQLOLEDB"
             .Open
         End With
     End If

I then used this to open a recordset as follows:

Function ADORst(strSQL) As ADODB.Recordset
Dim rs As ADODB.Recordset
On Error GoTo Err_ADORst

     mCnnInit
     'Create an instance of the ADO Recordset class, and
     'set its properties
     Set rs = New ADODB.Recordset
     With rs
        Set .ActiveConnection = cn
        .Source = strSQL
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
     End With
     Set ADORst = rs

Exit_ADORst:
     On Error Resume Next
     Exit Function
Err_ADORst:
     Select Case Err
     Case 0      '.insert Errors you wish to ignore here
         Resume Next
     Case Else   '.All other errors will trap
         Beep
         LogErr Err.Number, Err.Description, Erl, cstrModule, "ADORst"
         Resume Exit_ADORst
     End Select
     Resume 0    '.FOR TROUBLESHOOTING
End Function

And then in the form itself (onOpen) I did this:

set me.Recordset = ADORST("MySqlStatementHere") and

voila, my form is bound to an ADODB recordset.

This should make a huge difference to my applications, allowing SQL Server to do the work and just 
return the data.  And *supposedly* in all Access versions above 2000 the form is read/write.

Of course I now need to test under runtime.

-- 
John W. Colby
www.ColbyConsulting.com