Access VBA to link to online SQL table

Murray Brown
Murray Brown used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
In order for you to "Read" the data you have to be already "Linked' to it...

Can you explain what you are considering the specific differences between "Reading" and "Linking"?

In other words, simply state what you need to do...
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
I need to update my table in Access and have the SQL database automatically change. I don't want to create the link manually but rather with code. This is because I have to send the script to be run on many different computers
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:

Then I am confused,

<I need to update my table in Access and have the SQL database automatically change.>
Do you have a static table in Access that you are trying to take data from and update an SQL Server table with?
...this seems to be what the code you posted is doing.

In the loop you can do whatever you need, including Updating values.

Do While Not rst.EOF
    rst.edit
    rst!SomeSQLTableField=SomeTextBoxValueInAccess.
    rst.Update
    rst.MoveNext
Loop

Or, ...Is your Access table the "Linked" SQL table...you are trying to create via code?


In other words, give us a real-world data entry scenario...

"I have a table in Access of __________.
Whenever I change __________ in the Access table, I want __________ to be updated in the SQL table.

or perhaps I am just mis-understanding something...

Commented:
Murbro

If I am reading this right, you have an online sql database plus a few desktop access database all holding the same data and you want to synchronize them.

If so, then I am sure that most if not all expert will agree with me that this is not a good idea.What you need to do is split your database into front end and backend. Convert the backend to mysql and store on a central local server. Then run a daily backup to the online database.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Boag2000 - I merely posted that code to show the connection string
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi Sb9. The users are country wide so we cannot store on a local server. Also some users need to only see the data. One of those users needs to update it
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
murbro,

sb9 may be on to something...

Can you also clearly state the difference between the Access database table and your "online SQL table"?

Commented:
<The users are country wide so we cannot store on a local server. Also some users need to only see the data. One of those users needs to update it>

Then, I'd go for an online backend full stop.

<Also some users need to only see the data. One of those users needs to update it>

That can easily be control by user level permission in your front end
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Also note that Access 2007 and higher have "Email data collection".
This allows the DB to be updated via Email from any of the disparate locations, thus possibly eliminating the need for "Linking" altogether.

But again we need to know the specifics of this system, in case there is a more efficient alternative.

;-)

Jeff

Commented:
Hi Jeff

I need to head off to work, I'll leave this in you capable hands
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi boag2000. Would a better approach be to read the data from the online database into a temporary table and manipulate the data there.
I have already written most of the code. So for the manager that resides over the table, I read all the data into a temporary table in his Access database then sew which records are new on his side and append these back to the table.
Commented:
I have already written most of the code

You can keep all or most the codes. Just get rid of the local tables and replace them with linked tables from the online db.

This will improve the db in that the other users will have access to live information.

The only drawback may be performance since that local data are retrieve faster than online data. But since things are already working fine with the online version I don't think that this will be much of an issue
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
However, don't think that linked SQL tables are any sort of a "magic bullet". If you must have a truly performant app, you might need to move to an unbound mode, where you handle all data manipulation and just send DML statements back to your server (i.e. INSERT, DELETE, etc).
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thank you both. I am going to go with the unbound approach

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial