[Access 2003] ODBC linked tables -> how to create dsn

[Ecniv]

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

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.

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)

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

[nagasrikanth]

Well,,, No need to worry Ecniv,

1. Just Link the tables with the uid and pwd in ur database (Dont forget to Select Save password while linking)..
2. Then place ur "Create DSN Code" (Ofcourse, with out UNM and PWD).. in a module with in some Sub/Function..
3. Call the sub/function through a macro kind of thing..(I hope u heard abt AutoExec Macro)
4. Thats it.. All the users who open ur database can access the tables (won't prompts for any UNM/PWDs.. )

It works for sure..chk out once...
All the best .. Keep Rockin..

Regards,
Sri..

[Betadine]

Mmmmmmmm

strDSNString = "" <-- Bad habbit my friend

Please consider this:

As you can see, once a string is set to "", it
has a length of 0, but it is still taking up 6 bytes of memory. Once
the string is set to vbNullString, it is restored back to no memory
allocated.

from
http://www.xtremevbtalk.com/showthread.php?t=26717

Cheers

[Ecniv]

betadine:
thanks for the info - but
1) that code is copied from one of the links..
2) I am putting in a string on the next line - it's there to reset to nothing...


nagasrikanth:
ah hmm.. yeah about that linking. I've done this myself and there didn't appear to be an option to save (I know there should be...) Perhaps some security measure didn't allow it?

Problem is the code for creating the dsn doesn't work when I try to supply the uid and password. I don't think I have the right word/property name.
autoexec? hehehhe I use a form (splash screen) with code
This is hopefully going in as an addition - so I will request the users to press a button on the form before running any reporting.

thanks for the info