DSN-less connection for queries to SQL Server 1

[glgcag1991]

Converting a database backend to SQL Server. Still have a bunch of local tables that won't be converted (they act as temp tables) so I have local and SQL Server linked tables I am contending with.

This seems like a basic question, but I have a DSN-less connection programmed in a global variable, but when my existing queries that my combo boxes and list boxes are based on run, I still get a pop-up asking for my credentials to the SQL Server if the query references SQL Server tables. Does this mean I have to create a DSN for each user? Or, is there a way to initiate the DSN-less connection and once it's established, the queries will run?

As it stands, this doesn't seem to be the case since when I make a connection using ADO and the global connection variable is used, everything works fine, but as soon as I refresh a combo or list box, the pop-up appears.

Here is my global variable that stores the connection:
Const strConString = "Provider=SQLOLEDB;Data Source=SQLServ2;Initial Catalog=ProgData;Integrated Security=SSPI;"

Thanks for the help!

 

[vbajock]

When you set Integrated Security=SSPI in your connection string, security is going to be provided by SQL itself, you must have the users set up in SQL server and issue passwords, or you must hard code a password scheme into your connection string, something not real advisable if security is an issue. If security is not an issue and you want to use SSPI, create a "super user" on the SQL side, and then hard code that user and it's password into the connection string:

Your string would then look like this:

Const strConString = "Provider=SQLOLEDB;Data Source=SQLServ2;Initial Catalog=ProgData;Integrated Security=SSPI;UID=SuperUserName;PWD=mypass

More details here:

http://www.codeproject.com/Questions/194609/What-does-Integrated-Security-SSPI-means-in-the-Co

If possible, you will save yourself a world of headaches if you set up Windows Authentication for your users and use a Trusted Connection:

http://www.connectionstrings.com/sql-server-2008

 

[glgcag1991]

Thank you vbajock for the reply!

I am using Windows Authentication and the users already have read/write access with their existing network passwords, so Integrated Security is working. But I'm not sure how to get the queries on the forms and reports to work without a DSN. Any time I use ADO connection objects, I'm OK, it's just when a query is run.

Looking online I've found a couple of modules that link the SQL Server tables when logging on, using Windows Authentication, which seems like it might solve the problem, but relinking every time a user opens the database seems like a real pain.

 

[vbajock]

Hey, I can give you what works for me, I usually loop thru all my table defs on start up using my main menu's load event, calling a proc that will refresh the connection string, but I use an ODBC connection:

Public Function RefreshLinks()

Dim tdf As DAO.TableDef

On Error Resume Next
Dim stconnect As String

For Each tdf In CurrentDb.TableDefs

Select Case tdf.Connect <> ""

Case InStr(tdf.Connect, "DATABASE=MyDatabaseName")
stconnect = = "ODBC;DRIVER=SQL Server;SERVER=" & ServerName & _
";DATABASE=" & DatabaseName & ";Trusted_Connection=Yes"

tdf.Connect = stconnect
tdf.RefreshLink

Case InStr(tdf.Connect, "DATABASE=SomeOtherDatabaseName")
stconnect = = "ODBC;DRIVER=SQL Server;SERVER=" & ServerName & _
";DATABASE=" & SomeOtherDatabaseName & ";Trusted_Connection=Yes"
tdf.Connect = stconnect
tdf.RefreshLink

'etc keep adding case statements for different database attachments

End Select
Next

End Function

It will read the database in the existing string and then force a refresh based on the parameters you describe in the code. You can also use the look to pass passwords if the database is using SQL security. Run the loop when the user starts up, you can also call the proc anywhere else before your user runs a sql query if you want to be sure the connection is valid, because they do time out.

 

[vbajock]

Sorry for the typo - disregard the double "=" signs, also, you could probably get that to work with OLEDB if you play with the connection string.

 

[glgcag1991]

OK, I've tested and it works. Can I accomplish this with ADO?

Also, how do I know what the timeout is and compensate for it? Do you just call the refresh on a timer or do you call it before loading forms?

 

[vbajock]

No, you have to use the DAO tabledef object to access the connection string, I suppose you could do the same thing using ADOX, maybe someone else here has done it,

I usually just plug this function whereever I open a form based on a swl table, it seems to make the problem go away, even on large networks. Your biggest hassle if you are deploying it to a mixed network is going to be dealing with Access versions where DAO is not a default reference, you will have to add code to late bind DAO.