Connecting to SQL server backend

[ds2728]

Hello,

I have a application that I have successfully converted to use data on a sql server. I have no linked tables or DSN's for this application. I built the system using access 2003.

My problem is I have alot of access 2000 boxes that it needs to run on and it errors on the connection string that I am making to the sql server. below is the code that IU use to make my connection.

Public Function Init_Globals()

Set GBL_cn = New ADODB.Connection

With GBL_cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "xxxx-trustsql"
.Properties("User ID").Value = "username"
.Properties("Password").Value = "password"
.Properties("Initial Catalog").Value = "timedemo"
.Open
End With
EMP_search = ""
End Function

The following line is where it errors:

.Provider = "Microsoft.Access.OLEDB.10.0"

I have researched this and have found some things but none that really make it clear for me, partly because most references seem to be talking about linked tables thru a DSN, which I do not have.

Any help would appreciated on getting this working on access 2000.

Thanks,

Dave

 

[JerryKlmns]

Keep this handy

http://www.connectionstrings.com

 

[ds2728]

Thanks for the post, but I have the connection string working for access 2002-2007. My problem is with access 2000.

I get the following error:

----------------------------------
Run-Time Error 3706

Provider cannot be found
It may not be properly installed.
----------------------------------

Thanks in advance for any help

 

[dhookom]

Try changing
.Provider = "Microsoft.Access.OLEDB.10.0"
to maybe
.Provider = "Microsoft.Access.OLEDB.9.0"
or maybe
.Provider = "Microsoft.Access.OLEDB.8.0"


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ds2728]

Thanks, but no good. I actually tried the 9.0 earlier with no success then either. I did realize that I changed my connection string, so here's the new:

Public Function Init_Globals()

Set GBL_cn = New ADODB.Connection

With GBL_cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB.1"
.Properties("Persist Security Info").Value = True
.Properties("Data Source").Value = "psba-trustsql"
.Properties("User ID").Value = "DaveSchmidt"
.Properties("Password").Value = "bonehead"
.Properties("Initial Catalog").Value = "timedemo"
.Open
End With
EMP_search = ""
End Function

Thanks,

and again any help would be appreciated greatly.....

Dave

 

[JerryKlmns]

ds2728

You want a connection to the SQL Server and not to MS-Access.
In here look carefully at

# If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the LAST occurrence is used

# But!... if the PROVIDER keyword occurs multiple times in the string, the FIRST occurrence is used.

so your

   With GBL_cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB.1"

is exactly what is discribed there.

 

[JoeAtWork]

In other words, take this line out:

.Provider = "Microsoft.Access.OLEDB.10.0"


or better yet change to:

.Provider = "SQLOLEDB"