Connecting to SQL Database???

[ejc00]

I've got a slight problem... I have a Main form that essentially has tabbed forms on it. within one of the forms, there is a listbox which is based on a linked table from a SQL database. whenever the form loads, the user is prompted for their username and password. is there a way to open the connection when the form opens, and have the connection close when the form is closed? i have tried using an ADODB.Connection to connect to SQL, but this doesn't do any good. i have also tried including the a connect string in the sql string source of the listbox, but this doesn't work b/c the table is a linked table (which i'm unioning with a table in my access db).

i would appreciate any help anyone can give, as i don't have much knowledge in connecting to outside datasources.

Thanks,
evan

 

[RSGB]

Hi

I'm not an expert on this at all but when you link the tables you can select "You trusted connection" instead of a login and password I think on NT servers. This uses the network login so bypasses the issue.

Hope this helps

RSGB

 

[ejc00]

this would work... however, my network login is different from my login to the SQL server...

thanks for the tip

 

[RSGB]

Hi

I know but the SQL server can use trusted connection instead of it's own login thing.

I've tried it briefly 'cos I'm looking at upsizing the back-end to SQL server from Access. Maybe the SQL database needs to be configured to allow trusted source first...

Laterzzz

RSGB

 

[ejc00]

i just tried it... to no avail. i'm guessing the server does not accept trusted logins... and i don't have access to configure the server... any other ideas???

thanks again for your help...

ejc

 

[ejc00]

can i use some sort of connection string in VB to connect to the server that runs before the SQL string which queries the linked tables?

 

[MarcLodge]

I've linked tables using ODBC and I seem to recall the connection prompts you the first time and then stays open until you exit Access. Could you try it this way? HTH Marc

 

[cmmrfrds]

"is there a way to open the connection when the form opens, and have the connection close when the form is closed?"

Put the connection in the standard module. Define the ADO connection object as public with the scope at the module level. Open the connection when you enter the main form and leave it open until you exit the app. You can always close the connection just don't set it to nothing and it will be available to use until you no longer want it, then set it to nothing and it go out of scope.

Ideally the SQL Server should have both the SQL Server logons and the NT logons enabled that way you could have used the prior suggestion.

In SQL Server 2000, I am not sure about 7, there is an OpenRowSet that will allow you to combine both the Access and the SQL Server tables in a single sql statement. This would allow accessing SQL Server tables without linking through ODBC. I will paste in an example from an access program that uses both Northwind databases, the sql server and access. You should be able to run it to test it out.

Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If

End Function