Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access VBA to link to online SQL table

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

Microsoft Access

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Jeffrey Coachman

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 Brown

ASKER
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 Coachman


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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sheils

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 Brown

ASKER
Hi Boag2000 - I merely posted that code to show the connection string
Murray Brown

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

murbro,

sb9 may be on to something...

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

<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 Coachman

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sheils

Hi Jeff

I need to head off to work, I'll leave this in you capable hands
Murray Brown

ASKER
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.
ASKER CERTIFIED SOLUTION
Sheils

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Murray Brown

ASKER
Thank you both. I am going to go with the unbound approach
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.