Results 1 to 9 of 9

Thread: ODBC

  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    London, Gtr London, England
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC

    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 <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC

    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.
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2003
    Location
    Pennsylvania, USA
    Posts
    30
    Thanks
    0
    Thanked 3 Times in 1 Post

    Re: ODBC

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC

    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.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC

    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.
    Charlotte

  6. #6
    Lounger
    Join Date
    Apr 2003
    Location
    Pennsylvania, USA
    Posts
    30
    Thanks
    0
    Thanked 3 Times in 1 Post

    Re: ODBC

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC

    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.
    Charlotte

  8. #8
    Lounger
    Join Date
    Apr 2003
    Location
    Pennsylvania, USA
    Posts
    30
    Thanks
    0
    Thanked 3 Times in 1 Post

    Re: ODBC

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •