Need DSN -less help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need DSN -less help

Author

Topic

abacus
Starting Member

6 Posts

Posted - 2007-10-05 : 11:06:17

Hello- on a form I have in my Access 2000 database I would like to set up a DSN-less connection to SQL Server 2000. I would really appreciate some help as I am clueless. I just know I don't want to have to use ODBC because this is a front end that is distributed to about 20-30 users.

Here is what I need to do. When the form loads the connection is made to SQL. When the form closes, the connection is broken. I don't want to keep them connected all of the time because most of the users will never need to even use this form.

Here are my specific pieces:

Access DB Name:Projects.mdb

SQL Server Name:SServer001

SQL Server DB Name: BusinessSystem

SQL Server Table & View names (for connection)
dbo.invoices
dbo.orders
view_customers

Can someone help me with what the code to make this type of connection would be?

THANKS SO SO MUCH

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 11:09:33

Use SQL Server OLE-DB provider instead of ODBC provider.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

abacus
Starting Member

6 Posts

Posted - 2007-10-05 : 11:13:41

Tell me more, please. I am not familiar with how to do that or what that means. Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 11:19:31

While connecting to SQL Server, you will have to use connection string similar to something below:

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

abacus
Starting Member

6 Posts

Posted - 2007-10-05 : 11:22:06

Ok, just to show that I am not totally helpless here, I did find the following info relating to what you said:
Standard Security:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"

OK, so mine would be:

Provider=SQLOLEDB;Data Source=SServer001;Initial Catalog= BusinessSystem;UserId=abacus;Password=abacus_password;

Correct? But, um, now what?
So my code so far would be:

Option Compare Database
Option Explicit

Private Sub Form_Load()

Provider=SQLOLEDB;Data Source=SServer001;Initial Catalog= BusinessSystem;UserId=abacus;Password=abacus_password;

End Sub

How do I specify the tables and views?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 11:28:18

No.
You will have to use some kind of connection interface like ADO to connect to SQL Server using OLEDB.

Using ADO, you can do it like:

Dim oConn as Adodb.Connection

Set oConn = new Adodb.Connection
oConn.open "Provider=SQLOLEDB;Data Source=SServer001;Initial Catalog= BusinessSystem;UserId=abacus;Password=abacus_password;"

and then use ADODB.Recordset object to access your tables/views.

Dim oRS as ADODB.Recordset
set oRS = new ADODB.Recordset

oRS.Open "Select * from Foo", oConn
....

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

abacus
Starting Member

6 Posts

Posted - 2007-10-05 : 11:43:02

Ok I am going to set up a test right now and plug all this in and see what I get.
Thanks Harsh.

abacus
Starting Member

6 Posts

Posted - 2007-10-05 : 13:29:40

hmmm when I run it I get a run-time error "Invalid connection string attribute"

in debug, the following line is highlighted:
oConn.open "Provider=SQLOLEDB;Data Source=SServer001;Initial Catalog= BusinessSystem;UserId=abacus;Password=abacus_password;"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources