Login to SQL db when open Access database

[sphere_monk]

Relative VB newbie here.

I have an Access database that has some linked SQL tables in it. The problem is that users get a "SQL Login" screen the first time a field in a form is used that accesses the SQL tables after opening the Access database.

I know there is a way to login to the SQL database when you open the Access database, but I just can't put my finger on it in the books I have.

Could someone please point me in the right direction?

Thanks.

 

[crosmill]

you mean SQL Server?


I think problem is with access permisions on the SQL Server. if you give each user access rights to the (SQL Server) database with Windows Authentication then I think it will sort it out.

chris

 

[sphere_monk]

OK, I set up a user in SQL Server that has the same UserID that I am logged onto the Windows 2000 Server with (where SQL Server 7.0 resides) and given that user access to the database in SQL Server Security. (We're using Windows 2000 Pro on our workstations as well). I also added the following code to the Startup form's Load event:

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

With cnn
.Provider = "SQLOLEDB"
.Properties("Data Source") = "PowerEdge1300"
.Properties("Initial Catalog") = "BWD"
.Properties("Integrated Security") = "SSPI"
.Open
End With

I restarted the database and still get that pesky SQL Login screen when I try to input data into a linked table field for the first time. Did I miss something?

 

[crosmill]

hmmmm ok, lets go back a few steps. Is there reason your coding the conneciton yourself. Can you not just use the wizard in Access to set it up?

 

[sphere_monk]

I'm not sure what you mean....

What I have done is linked some of the tables in the SQL Server db so that I can access some of their info on the forms along with the data from the Access tables. The database type is a regular Access database (.mdb) and not an Access Project, so I thought that it wouldn't really be a relationship that I would set up with a wizard. Is there a wizard I can use to set this up?

 

[crosmill]

yeah, if you click the tables tab on your database manager thing in access then right click and link table. it'll walk you though the setting up of the dsn, it then saves the connection so you don't have to do it every time.

I don't know how familiar you are with that sort of stuff, and it's not the most user freindle of wizards so post back if you need any help.

oh, when you link the table you need to change the file type to odbc Databases

hth

 

[sphere_monk]

OK, I figured it out......

I had already linked these tables originally using the Wizard you were talking about. What I had failed to do was select the little checkbox marked "Save Password" after I had selected the tables to link. I deleted all my previously linked tables that were not storing the password and simply re-selected them with this checkbox checked and "Viola!" no more pesky SQL Login popup screen!

I was spending a lot of time looking in the wrong place for a solution to this one. Thanks for pointing me in the right direction!