We help IT Professionals succeed at work.

Access VBA - querying SQL Express 2008 database

Murray Brown
Murray Brown asked
on
Hi

Is it possible to query and write to a SQL Express 2008 database that sits online?
How would I do this, ie what VBA code would I use? Thanks
Comment
Watch Question

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
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:

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.Open "Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;"

'/ Note that connection string from here: http://www.connectionstrings.com/sql-server-2008#p2

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM MyTable", 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
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much