Link to home
Start Free TrialLog in
Avatar of swedishtongue
swedishtongueFlag for United States of America

asked on

Creating an ODBC connection via VBA utilizing a generic username and password

i have created the following code to automatically set up an ODBC connection in my access database. this code works great if i use the trusted connection option but when i try and give it a generic user name and password it fails. Anyone know what i am doing wrong?

PS i have manually set up the user name and password and they work just fine
Option Compare Database
Option Explicit
'Const ODBC_ADD_SYS_DSN = 1      'Add a user data source
Const ODBC_CONFIG_SYS_DSN = 2    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 3    'Remove data source
Const ODBC_ADD_SYS_DSN = 4       'Add a system data source
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
   hwndParent As Long, ByVal fRequest As Long, ByVal _
   lpszDriver As String, ByVal lpszAttributes As String) As Long
Function Build_SystemDSN()
    Dim Driver As String
    Dim Ret As Long
    Dim Attributes As String
    Driver = "SQL Server"
    'attributes are the connection information
    Attributes = "server=ANSRLTR" & Chr(0)
    Attributes = Attributes & "DSN=DSN_Temp" & Chr(0)
    Attributes = Attributes & "Database=ANSRLTR_Prod" & Chr(0)
    'use this line if you want to use the users name and password
    'Attributes = Attributes & "Trusted_Connection=Yes" & Chr(0)
    'use this line if you are adding a username and password
    Attributes = Attributes & "Uid=Temp;" & Chr(0) & "Pwd=TempID" & Ch(0)
      Ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)
   'ret is equal to 1 on success and 0 if there is an error
   If Ret <> 1 Then
       MsgBox "DSN Creation Failed"
   End If
End Function

Open in new window

Avatar of Serge Fournier
Serge Fournier
Flag of Canada image

you can open the dsn file you created manually
then you will see what parameter is missing in your connection string
(use notepad to open a .dsn file)

Avatar of swedishtongue


unfortunatly i have tried that and i dont seem to be missing anything.
oops i forgot to chek the skill level of the asker ;)
LOL somedays my skill level is better than others !)
Avatar of Serge Fournier
Serge Fournier
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
in fact
you can extract the connection you create manually from register base (export)

then modify this script to write them again
i see in your code you used the trusted connection=yes. i dont want to use the trusted connection but add a userid and password. my code isnt working so i would be interested in how you would write that.
err, yeah this is not a dsn i am making

just a new odbc connection
do a new connection, in odbc, to your access file, with logon and password
then export the registry keys
then with this script, recreate the connection dynamically, by writing the same registry keys

i think your goal is to be able to create a connection dynamically yes?
even if it's not a dsn?
if it's too much work
i can test it here, but it will be with my access file
and i do not know if yours is protected with a password
or if you really need it to be a dsn connection
sorry i forgot to add that i am trying to set up the connect for a SQL database so it has to be password protected or a trusted connection. I have over 100 users that will be using this database and dont want to have to assign them the rights to the SQL database so i am trying to just add a generic user name and password.
PS the code i wrote works for the trusted connection but not when i supply the username and PW. what i am wondering is, did i write it incorrectly? if so what am i missing? or what should the code be?

its kind of driving me nuts that i cant figure it out :(
just wait 2 min i am making one, then exporting the registry, then modifing my script
oops i just hit a wall, that i did not remmeber before
odbc will not store a sql connection password

why don't you connect to sql directly? use a system variable for server name if you want to stay dynamic

there i create a sql connection with logon and pass
then execute a sql query
then put all results in an array for treatement
(altought it can be left in the recordset to manipulate it also)

after that, i remove the null for excel compatibility, but it's not necessary

more details, if you agree to use a direct connection, not a dsn

example in vba:

   set database=createobject("adodb.connection")
   set tag = CreateObject ("ADODB.Recordset")
db_host = environ("stassql1") '=== sql server name
db_database = "winventory"
db_user = "sa"
db_password = "testtttt"
conn = "driver={SQL Server};server=" & db_host & ";Database=" & db_database & ";Uid=" & db_user & ";Pwd=" & db_password conn
  sql = "USE " & db_database
  set tag = database.execute(sql)
myarray = TAG.GetRows()
xmax = UBound(myarray, 1) 'Returns the Number of Columns
ymax = UBound(myarray, 2) 'Returns the Number of Rows
'=== transpose grid (reverse x and y)
'Set a = Range(Cells(y, x), Cells(y + ymax, x + xmax))
For xx = 0 To xmax
   For yy = 0 To ymax
      If IsNull(myarray(xx, yy)) Then myarray(xx, yy) = ""

Open in new window

err my query (sql = "use database") is not really a query as you can see

you must change it for your query
okay i will give it try

call if you need help
like a loop to read all columns in the dataset
then another loop to read all line in the database
thanks i am leaving work now so i wont get around to it until Monday.