Connection To sql server

[Van T. Dinh]

****Untested****
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB; Server={YourServer};" & _
"Database={YourDatabase};" & _
"uid={UserName}; pwd={Password}"

'(above is for SQL Server Authentication)

Replace the last line with:

"Trusted_Connection=Yes"

for MS Windows NT Authentication.

 

[Roy Goldhammer]

Hello there

I need to create the connection to sql server by code.

Can i do this?

any help would be useful

 

[Roy Goldhammer]

Thankes Van

How can i do this for the currentProject.Connection?

 

[r]

Once you have created the connection using the wizard, where do you find the
code you have listed below?

Thanks!

T

 

[Van T. Dinh]

I am not sure of your question but if you meant you want to create a
Connection Object in code which is the same as your ***ADP*** Connection
then simply use:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

If you are using ***MDB***, the CurrentProject.Connection points to your
MDB, not SQL Server so it is of no help in this case.

Re-post with more details if I guessed incorrectly.

 

[Van T. Dinh]

Are you talking about ADP?

If it is the case, use the Menu File / Connection ... to get the ADP
Connection.

 

[Joel_Banks]

When I try to use the connection wizard, the SQL db connects for a flash and
then disconnects. I have changed everything that I could in wizard. Any
ideas?

Thanks in advance.

Joel Banks

 

[Van T. Dinh]

Not sure but have you got appropriate permission to the SQL Server?

--
HTH
Van T. Dinh
MVP (Access)

 

[Joel_Banks]

Yes I do, I can test the connection and it is okay. Also, the ODBC link
works fine.

Thanks,

Joel

 

[Van T. Dinh]

Sorry, got no ideas as Connection Wizard works fine for me to date.

 

[BJ Freeman]

do you have any start up code that could effect this?

 

[Roy Goldhammer]

Well Van.

The problem is this?

sometimes on my computer the connection to sql server is being stoped and i
need to reconnect it again.

The way i solve this so far is by entering to file -> Connect and reconnect
to sql server. After that i create a function that tests the connection to
sql server on any form i open and if the ADP is not connected it runs the
file -> connect menu automaticly. The problem is that i can work with this
but the client will never do such a thing like that

so my question is: can i do that the file -> connect menu order do with my
code: connectiog to sql server?

 

[Van T. Dinh]

Can test at the moment but there is a command:

DoCmd.RunCommand acCmdConnection

you may like to try (I never used it, though).