Get rid of SQL Server Login Box from Access mdb database

[Alibaba2003]

I have an application with ms-access 2000 as its front end and sql server 2000 as the backend. I have created an ODBC system data source name DSN. Each Time I open the database I am prompted for SQL Server login. I need a way to store that information in the database without the users being involved. I will always be using 1 account on sql server. Thanks
Alibaba

It's Nice to Be Important But It's more Important to BE Nice

 

[smedvid]

It sounds like you are using linked tables via ODBC...

1. Perhaps - Create DNS-less Connection to SQL Server. There are tons of examples to search on. Basically, it removes need to have an entry in ODBC registry. Then on system start up link all tables required. I do this now for several systems.

2. Consider using ADO exclusively throughout your system. I've done this also for a small scale front-end to a SQL Server backend.

3. Look at using MS Access ADP. The connectivity to SQL Server is seamless and fast. I am also using this approach for a system.

htwh,



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Alibaba2003]

Thanks ..
One think that you need to drop and relink all of the tables. But dropping and refreshing the link on one table is good enough to get rid sql server logon box. Here is the function I came up with .. you can replace the table accocunts with any table you have in your system ..

Function GET_RID_OF_SQL_LOGON_BOX()
On Error GoTo Err_btn_Update_SQL_LINKHIST_Click

    Dim dbsCurrent As Database
    Dim tdfLinked As TableDef
    Dim strConnection As String
    Dim strSourceDBConnectionLine As String
    Dim ArrayLength As Long
    Dim ArrayIndex As Long
    Dim strErrMsg As String
    Dim FlagErrMsg As Boolean
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentData
    


    FlagErrMsg = False
    strErrMsg = " You Have Errors: " & Chr(13) & Chr(10)
    
    
    

    ' Open a database to which a linked table can be
    ' appended.
    
    
    
    Set dbsCurrent = CurrentDb
    
    
     strSourceDBConnectionLine = "ODBC;DATABASE=PDS_DATA_PROD_VER5;UID=sa;PWD=pds2005;DSN=PDS_DEV_LOCAL_05"
    
    
    
    
    Dim tempName As String
    
    
    
    
    ' Search for open AccessObject objects in AllTables collection.
    For Each obj In dbs.AllTables
    tempName = obj.NAME
        If tempName = "accounts" Then
            ' Print name of obj.
           dbsCurrent.TableDefs.Delete tempName
           
       
       Set tdfLinked = _
        dbsCurrent.CreateTableDef(obj.NAME, , obj.NAME)
        tdfLinked.connect = strSourceDBConnectionLine
        tdfLinked.SourceTableName = obj.NAME
        dbsCurrent.TableDefs.Append tdfLinked
    
    'ArrayIndex = ArrayIndex + 1
    
    End If
    
    Next obj
    
    
  

    dbsCurrent.Close
    
    Set dbsCurrent = Nothing
    
    ' MsgBox ("Connection to AIMS History Data HAS Been Successfully Refreshed")
    
    
Exit_btn_Update_SQL_LINKHIST_Click::
    Exit Function

Err_btn_Update_SQL_LINKHIST_Click:
    MsgBox err.DESCRIPTION
    Resume Exit_btn_Update_SQL_LINKHIST_Click:
End Function

It's Nice to Be Important But It's more Important to BE Nice