Connection string

[accessman2]

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection

conn.Open "Provider=sqloledb;" & _
"Data Source=localhost;" & _
"Initial Catalog=db1;" & _
"Integrated Security=SSPI"

Set rs = conn.Execute("sp_check")


I want to write the code to connect SQL Server. But, why it doesn't work?
Is anything wrong there?

Thanks.

 

[boblarson]

Yes, there is -

conn.Open "Provider='sqloledb';" & _
"Data Source='localhost';" & _
"Initial Catalog='db1';" & _
"Integrated Security='SSPI'"

As shown you need to add single quotes.

 

[RoyVidar]

1 - I think one is encouraged to revisit ones old threads, and tell about whether the challenge is is solved or not, which solution was selected/worked etc, so that the ones who bothered to reply to your question, continue to do so, and also for other members with the same challenge who search the fora can find out the solution. Here are som recent ones
http://www.access-programmers.co.uk/forums/showthread.php?t=120015
http://www.access-programmers.co.uk/forums/showthread.php?t=120014
http://www.access-programmers.co.uk/forums/showthread.php?t=119950

2 - I think one is encouraged to give a bit of information about what the error is, which error number, what actually happens, what you wish should happen etc, not just "But, why it doesn't work?", which isn't very much to work on, for someone who wish to assist.

3 - replace

set conn = currentproject.connection

with

set conn = new adodb.connection

in your original code. For more connection strings etc, have a look at http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

 

[Moniker]

Roy is correct. Explicitly make the connections, like this:

Dim rsMyRecords As Recordset

Set rsMyRecords = New Recordset

With implicit declaration, you're asking for all sorts of weird problems should the structure get more complex.

He's also correct in (almost) patiently pointing out the search features of this forum.

~Moniker