ODBC

Topic

New Reply

When I set an ODBC link from my Access front end to the SQL Server backend, why doesn’t my System DSN remember the username and password that I have supplied?

Please help

Reply | Quote

Replies

Security. You can hard code the userid and password into the connection string, but it isn’t a good idea from a security point of view.

Reply | Quote

Charlotte,
Is there a way to include everything needed to create an ODBC connection in an access module so that the connection does not have to be configured on each workstation. I know a file DSN can be used but they get tricky with hard drive paths etc as well. I am hopping that something can be created like works in an ASP page. I am not concerned about putting the user id and password in the module as that particular user will be severely limited by the SQL Server permissions anyway.

Thanks in Advance
hr

Reply | Quote

Which version and SR of Access are you using? If you’re in 2000, you can use an ADO connection instead of ODBC and all that requires is the correct provider string. However, to log into the SQL Server, you’re still going to have to either use Windows logins or valid SQL Server logins, depending on how you set up the connection. Please tell me you weren’t intending to log everyone in as sa and with no password, because if you are, you might as well shoot yourself in the foot now and save a lot of time.

In fact, with ADO you don’t have to link the tables at all, but your forms would all have to be unbound to be editable, which doesn’t work if you have a continous form.

Reply | Quote

Thinks a bit more, would it be possible for you to use a UDL? That holds the entire connection–provider string, path, everything–in what is essentially a text file. As long as the mappings are the same (I’m assuming the SQL Server is on a LAN), the same UDL file should be usable from any machine. Then all you need to do is create an ADO connection and pass it the path and filename of the UDL file for its connection property.

The route you choose depends on exactly what you want to do with the connection.

Reply | Quote

Actually I am using a little utilized feature of ODBC called ODBC Direct which lets me execute a stored procedure and return a record set. Permission can then be set on the stored proc for the ODBC user. Obviously I am not using SA, I said that the user login contained in the code would be severely limited so it couldn’t be SA. I have a mixed user group of Access 97 SR2 and Access 2K SR1. I can code exclusively for Access2kSR1 if its the only way but would like not to exclude those that haven’t been migrated yet. I have everything working currently but the specified ODBC connection must be installed on each workstation.

Can you explain the ADO thing or point to a reference.

Reply | Quote

It’s way too complex to explain quickly but it is essentially the next generation from ODBC direct. Check this post for a link to a reference site.

Reply | Quote

I found the following at the provided link:

______________________________________
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset
Dim iTotalConflictingRecords As Integer

‘ Create and open a new connection to the Pubs database
Set oConn = New ADODB.Connection
oConn.Open “Provider=sqloledb;” & _
“Server=(local);” & _
“Initial Catalog=pubs;” & _
“User ID=sa;” & _
“Password=;”

‘ Create a Command object and a it’s Parameter object
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = “authors_load”
oCmd.Parameters.Append oCmd.CreateParameter(“au_id”, adChar, adParamInput, 11, “172-32-1176”)

‘ Create and open an updateable Recordset (passing in the Command object)
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseClient
oRS.Open oCmd, , adOpenStatic, adLockBatchOptimistic

‘ If author record was found
If Not oRS.EOF Then
‘ do something with the recordset
End If

__________________________________________

But it appears to have some constants that are unexplained and I don’t need or want an updatable recordset. A snapshot(?) with no record locking would be more appropriate for my use since the resulting recordset will be placed in a temporary Access table.

Any suggestions?

It would also seem that the workstations using this type of SQL connectivity would have to have MDAC and a SQLServer provider installed on each one and I hear that its very difficult to determine the installed version to insure compatibility. Is this true also

Reply | Quote

You have to have MDAC installed because that’s what installs ADO. Determine the version of what, ADO or SQL Server?

MSDE and SQL Server are the same thing when it comes to linking to them running stored procedures, etc. The difference is that MSDE is optimized for something like 5 users and crawls at any more, plus it doesn’t have the enterprise manager or the other tools that come with SQL Server, only DTS. You can connect to a SQL Server on a network based on the site licenses for that server. If you need a seat license, each copy of Office 2000 has one along with MSDE. But you probably already have adequate licenses with your network installation, or you wouldn’t be able to work with Access 97 and SQL Server.

The code you posted is pointing to a SQL Server or MSDE database on the local drive; but if you’re using a network drive, you change that code to point to the actual SQL Server or simply use a UDL file to hold the entire provider and connection string and then set the connection in code by pointing at the UDL file path and name. That’s much easier than creating the connection strings in code and has the benefit of being flexible. You can change backends simply by changing the UDL file contents.

The constants are ADO constants, which means that they are different from the DAO constants you’re familiar with. There isn’t a snapshot recordset in ADO, although the static recordset specified in the code you posted is the rough equivalent. There isn’t a NoLocks lock type on an ADO recordset either. BatchOptimistic is your best best since the records are only locked when you apply a batchupdate, which you aren’t going to do.

However, if all you want to do is build an Access table, why not use a query instead of opening a recordset? If you have a maketable query in Access, you can run it using ADO code and handling it like a stored procedure, which is essentially what it is.

Reply | Quote