vba - How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010? -

vba - How do I setup an ADODB connection to SQL Server 2008 in Microsoft Access 2010? -

i installed sql server 2008 on laptop. have microsoft access 2010 installed. using vba, trying create adodb connection own database on sql server i'm having problem finding right line of code:

when utilize below, doesn't work. name of computer laptopx , database homesql.

i sure it's super easy since i'm starting out can't seem find right way inquire question.

thanks!

dim dbcont object set dbcont = createobject("adodb.connection") dim strdbpath string strdbpath = "laptopx/homesql" dim sconn string sconn = "provider=microsoft.ace.oledb.12.0;" & _ "data source =" & strdbpath & ";" & _ "jet oledb:engine type=5;" & _ "persist security info=false;" dbcont.open sconn

first, need create sure sql native client instaled. reference

sql server 2008

standard security

provider=sqlncli10;server=myserveraddress;database=mydatabase;uid=myusername; pwd=mypassword;

trusted connection

provider=sqlncli10;server=myserveraddress;database=mydatabase; trusted_connection=yes;

connecting sql server instance syntax of specifying server instance in value of server key same connection strings sql server.

provider=sqlncli10;server=myservername\theinstancename;database=mydatabase; trusted_connection=yes;

source

dim conn new adodb.connection dim cmd new adodb.command dim sconnstring string dim recordsaffected long 'create connection string sconnstring = "provider=sqloledb; server=laptopx; database=homesql; trusted_connection=true;" 'open connection , execute conn.open sconnstring 'do query cmd .activeconnection = conn .commandtype = adcmdtext .commandtext = "select ...;" .execute recordsaffected 'includes homecoming parameter capture number of records affected end debug.print recordsaffected 'check whether records inserted 'clean if cbool(conn.state , adstateopen) conn.close set cmd = nil set conn = nil

sql-server-2008 vba adodb