Access VBA - querying SQL Express 2008 database

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

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