troubleshooting Question

Access VBA to link to online SQL table

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Access
14 Comments2 Solutions344 ViewsLast Modified:
Hi

I was using the following code in Access VBA to read from my online SQL table.
How do I write similar code to link to that table
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Sub ConnectToSQLServer()

    On Error GoTo EH
    'Assuming the SQL Express database is configured for remote connections you can. You generally do this by opening a connection to the database and then issuing your query:
    'Ref Microsoft AxtiveX Data Objects 6.0 Library
  
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=sqlserver1;User ID=mubro;Password=chet;"
     
    'sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    '/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2
    ' murbro: Connection Information:   sql7.[yourdomain.com],1444 (196.220.43.247) (Web Manager) (Port 1444)
    
    Set rst = New ADODB.Recordset
    
    rst.Open "SELECT * FROM Table1", con
    
    'These are the basics, but this would leave you with a fully filled Recordset.
    
    'Note that unless your online host has assigned a name for this, you'd have to use the full IP address as "Server" portion
    
    Do While Not rst.EOF
        MsgBox rst.Fields("Col1").Value
        rst.MoveNext
    Loop
    
    rst.Close
    
    Set rst = Nothing
    Set con = Nothing


Exit Sub

EH:
    MsgBox Err.Description
    
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros