Connection String to local SQL Server 6

[Cloonalt]

I have a connection that works when connection to a SQL server at work. I'm trying to work at home with a local SQL server and the string doesn't work. Can anyone help?

strCnxn = "Provider='sqloledb';Data Source=(local);" & _
"Initial Catalog='Daily_Import';Integrated Security='SSPI';"

Thanks.

 

[OhioSteve]

The simplest way to get started on the problem would be to automatically generate a conn string. Visual Studio can do that. Do you have VS on your home machine? If that is not an option see below.

 

[OhioSteve]

This is the string I use to connect an MS Access DB with an SQL Server DB on the same domain, but not the same machine.

Provider=SQLOLEDB.1;
user id=xxx;
password=xxx;
Persist Security Info=False;
Initial Catalog=xxx;
Data Source=xxx


A connection string can include dozens of arguments. But in my experience, these arguments are mandatory. If I don't have these arguments, the connection won't work. The unfortunate thing for you is that my experience (on this topic) is limited. So I cannot say whether EVERY ole connection from Access to SQL Server requires these arguments.

Here is some more info/comments on the arguments:

Provider- In asp.net you choose this first. I get the impression that its some kinda communications protocol. Oh, I noticed that yours doesn't have ".1" at the end.

user id & password- The sql server account's credentials.

persist security info- no idea what this does

initial catalogue- thats the DB space name

data source- thats the server's name or IP address.

 

[cmmrfrds]

Use the UDL Wizard to create the connection string and then copy into your app.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft sql server provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[Cloonalt]

cmmrfrds, that's the best. I would give you more than one star if I could. I've never seen that before

 

[OhioSteve]

I gave you another star cmmrfrds. That is incredible...it is like Windows Voodoo.

 

[cmmrfrds]

Thank you. I came across it about 3-4 years ago when developing an ASP app. It has been useful many times.