MS access database calling SQL database with login 2

[tbark]

I have an Access database with linked tables to an Altiris SQL database. I have built a query in Access to find Inventories older than 90 days. The query works in Access. (I have to enter a password for the ODBC connection to Altiris) I am creating a web page to display these Inventories, but I get an ODBC connection failed in Dreamwearver. I have a connection to the AltirisDB as well. I want to embed the username and password in some way for my Access database to pass to the Altiris for this query. Is this the way to do it or am I missing somethig?

 

[boblarson]

When you initially link to the SQL table from Access, you should have a Save Password checkbox that is available to you when you enter your username/password. However, it is stored visibly to anyone who can get access to the MsysObjects table.

 

[tbark]

There is not an option to Save Password.

 

[boblarson]

 

[lespaul]

and that's why a picture is worth a thousand words!

Leslie

Have you met Hardy Heron?

 

[JoeAtWork]

Why not just get the data directly to your web app from the SQL server?

 

[tbark]

Thanks Bob for your post. I never noticed that box before. That did it!!!

 

[boblarson]

Glad to be of assistance.

 

[WyldeCarde]

Is there anyway to get the database to remember the password without setting up the links again?

Sorry for the necro post, but this is exactly what I was looking for.

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[boblarson]

No, you have to set the links again if you didn't set the password to be kept the first time.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[JoeAtWork]

Actually, you can with code:

Public Sub ChangeSQLConnection(NewConnection As String)
    Dim dbs As DAO.Database
    Dim tdf As TableDef
    
    Set dbs = CurrentDb
    
    For Each tdf In dbs.TableDefs
        'Only change tables linked to SQL server
        If Left(tdf.SourceTableName, 4) = "dbo." And Left(tdf.Name, 4) <> "MSys" And _
         tdf.Connect <> "" Then
            tdf.Connect = NewConnection
            tdf.RefreshLink
        End If
    Next
    
    Set tdf = Nothing
    Set dbs = Nothing
    
    MsgBox "Finished Relinking!", vbInformation, "Finished"
End Sub

The connection string you pass could be something like:

ODBC;Driver={SQL Native Client};Server=MyServer;Database=MyDatabase;Uid=MyLogin;Pwd=MyPassword;

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[WyldeCarde]

Ahh right

With the number of different data sources we've got that wouldn't be very practical, but thanks for the tip!

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************