ADODB Local Server Connection

[medberg]

Hey Everyone,

In my workbook I am trying to connect to a local server using an ADODB conneciton referening the activex data objects 2.8 lib. I am using code that I copied from an old project of mine that works just fine, however it is not working when I modify it to use (local) as the server, rather than an actual server name. The old code is:

    Dim cnt As ADODB.Connection
    Dim cmd As New ADODB.Command
    Set cnt = New ADODB.Connection
    cnt.ConnectionString = "Provider=SQLNCLI;DATABASE=" + database + ";SERVER=" + server + ";Integrated Security=SSPI;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DataTypeCompatibility=80;"
    cnt.Open
    cmd.ActiveConnection = cnt
    exportQuery = query
    cmd.CommandText = exportQuery
   cmd.Execute

The new code is the same, with (local) replacing server in the above code. Does anyone have any idea as to why this is not working? Is it maybe not possible to connect to a local server using this type of connection? Thanks so much in advance.

-medberg

 

[bensonsearch]

you need to tell it where to look for the database eg
c:\program files\database.mdb

just saying local would be saying somewhere in this computer but im not telling

 

[medberg]

Wow ha...thanks. We had been using SQL Native Client for a couple select statements using (local) and that worked just fine, apparently you can't do the same for ADODB connections though.

Thanks!

 

[bensonsearch]

lol, no problems at all. glad it helped