Hi,


Been looking around but cannot find what I need.

I have an Access mdb with linked tables to an SQL server.
I have a user ID and password to connect.

When I open the tables/query using those tables a pop up appears requesting the password.

I had to create a DSN (system) and now I have been asked to produce a form for other people to use to run reports off those tables.

so here is what I need...
1) create a DSN on a machine via vba (code I currently have below)
* I cannot set the user id and have tried : user, uid, userid, logon login, logonid...
* Using the api function
Code:
Public Declare Function apiSQLConfigDataSource Lib "odbccp32.dll" Alias "SQLConfigDataSource" (ByVal hwndParent As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
References so far:
DevX entry (initial starting place)
Free VB Code went for further information when bits were missing from the initial place.
MS Support... but uses registry which is locked to me... at least directly locked.

Code:
Function PrepareDSN(ByVal strServerName As String, ByVal strDBName As String, ByVal strDSN As String, ByVal strDBUser As String, ByVal strDBUserPassword As String, ByVal strDescrip As String, ByVal strODBCDrv As String) As Boolean
'---- function to create a dsn
    On Error GoTo error_hdl
    
'requires
'   Server name
'   Database Name
'   DSN
'   DB User
'   DB Pwd
'   Description
'   ODBC (Visual) Driver description
    
'PrepareDSN ("GAY02016", "Veritape","Veritape" ,"user" ,"pwd", "Veritape Database","SQL Server"
    
    
    Dim boolError As Boolean
    Dim strDSNString As String
    Const max_Buffer_Size As Long = 1024
    Const ODBC_Add_DSN As Long = 1 'user
    Const ODBC_ADD_SYS_DSN = 4
    
    PrepareDSN = False
    
    strDSNString = Space(max_Buffer_Size)
    strDSNString = ""
    strDSNString = strDSNString & "DSN=" & strDSN & Chr(0)
    strDSNString = strDSNString & "DESCRIPTION=" & IIf(Len(strDescrip) = 0, "DSN Created Dynamically On " & CStr(Now), strDescrip) & Chr(0)
    strDSNString = strDSNString & "Server=" & strServerName & Chr(0)
    strDSNString = strDSNString & "DATABASE=" & strDBName & Chr(0)
    strDSNString = strDSNString & "Trusted_Connection=No" & Chr(0)
    strDSNString = strDSNString & "LoginID=" & strDBUser & Chr(0)
    'strDSNString = strDSNString & "pwd=" & strDBUserPassword & Chr(0)
    strDSNString = strDSNString & Chr(0)
    
    If Not CBool(apiSQLConfigDataSource(0, ODBC_ADD_SYS_DSN, strODBCDrv, strDSNString)) Then
        boolError = True
        'MsgBox ("Error in PrepareDSN::SQLConfigDataSource")
    End If
    
    PrepareDSN = Not boolError
    Exit Function
    
error_hdl:
    MsgBox "PrepareDSN_ErrHandler::" & Err.Description
End Function
I am supplying (uid and pwd removed)
Code:
PrepareDSN("ServerName", "Nameit", "Nameit", "UID", "PWD", "NAmeit Database", "SQL Server")

2)
Using DAO preferably, how would I open a connection to the SQL server, using the DSN, and supply a password (as it askes for it when accessing the tables).

I think that is it for now. Thanks in advance