Help with ODBC code

[DaveMac]

I am out of my comfort zone and it shows.

I would like to create a command button that creates an ODBC connection for users automatically. I do not care if it is “on the fly” or if the created connection is now present in their ODBC system connections.

I have 2 tables in the DB that get linked tables and drive everything else.

I have not figured out the linked table issue yet or even if I need to given an ODBC connection may allow the linked tables to connect properly.

The user set is 20 users in 20 different states connecting back to 1 data warehouse.

Please save me from setting up 20 ODBC connections and then the 20 backup people.

What I have so far is:

A command button that looks like this

Private Sub CreateODBC()
On Error GoTo Err_CreateODBC_Click
getMySqlConnectionString

Exit_CreateODBC_Click:
Exit Sub
Err_CreateODBC_Click:
MsgBox Err.Description
Resume Exit_CreateODBC_Click
End Sub

--Then a function that looks like this

Public Function getMySqlConnectionString(ByVal myDatabase _
As String, ByVal blnLinkedTable As Boolean) As String

Dim UserName As String
Dim Password As String
Dim ServerAddr As String
Dim Driver As String
Dim Port As String
Dim strODBC As String

oConn.Properties("Prompt") = adPromptAlways

oConn.Open "Provider=SQLNCLI;Server=svrdw;DataBase=sales;"

If blnLinkedTable Then
strODBC = "ODBC;"
Else
strODBC = ""
End If

getMySqlConnectionString = strODBC & "Driver=" & Driver & ";" & _
"svrdw" & ServerAddr & ";" & _
"sales" & myDatabase & ";" & _
"What Port is MS SQL DB????" & Port & ";" & _
"Testuser" & UserName & ";" & _
"testuserpassword" & Password & ";" & _
"Option=3;"

End Function

As you can see this does not work. I made changes and assumptions that are beyond my skills. Also I have no idea what port to use.

Thanks for any help

 

[dhookom]

I typically use code modified from Doug Steele's page for DSN-less connections.

Duane
Hook'D on Access
MS Access MVP

 

[DaveMac]

How can I tell if the code worked?

I ran FixConnections "svrdw", "sales"

in the ctl-G window

with code listed below but in my linked tbls I do not see any change in linked tbl mgr? Any suggestions?

Sub FixConnections(ServerName As String, DatabaseName As String)
' This code was originally written by
' Doug Steele, MVP djsteele@gmail.com
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This subroutine looks for any TableDef objects in the
' database which have a connection string, and changes the
' Connect property of those TableDef objects to use a
' DSN-less connection.
' This specific routine connects to the specified SQL Server
' database on a specified server. It assumes trusted connection.
'
' Inputs: ServerName: Name of the SQL Server server (string)
' DatabaseName: Name of the database on that server (string)
'

On Error GoTo Err_FixConnections

Dim dbCurrent As DAO.Database
Dim prpCurrent As DAO.Property
Dim tdfCurrent As DAO.TableDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strDescription As String
Dim typNewTables() As TableDetails

intToChange = 0

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

' Build a list of all of the connected TableDefs and
' the tables to which they're connected.

For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
ReDim Preserve typNewTables(0 To intToChange)
typNewTables(intToChange).Attributes = tdfCurrent.Attributes
typNewTables(intToChange).TableName = tdfCurrent.Name
typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
typNewTables(intToChange).Description = Null
typNewTables(intToChange).Description = tdfCurrent.Properties("Description")
intToChange = intToChange + 1
End If
Next

' Loop through all of the linked tables we found

For intLoop = 0 To (intToChange - 1)

' Delete the existing TableDef object

dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName

' Create a new TableDef object, using the DSN-less connection

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

' Where it existed, add the Description property to the new table.

If IsNull(typNewTables(intLoop).Description) = False Then
strDescription = CStr(typNewTables(intLoop).Description)
Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText, strDescription)
tdfCurrent.Properties.Append prpCurrent
End If

' Where it existed, create the __UniqueIndex index on the new table.

If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
Next

End_FixConnections:
Set tdfCurrent = Nothing
Set dbCurrent = Nothing
Exit Sub

Err_FixConnections:


Select Case Err.Number
Case 3270
Resume Next
Case 3291
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL, _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
Case Else
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
End Select

End Sub

 

[DaveMac]

Answered my own question. I changed the ODBC on my PC and then tried to open the linked tbl and the link failed. So it looks like the linked tbl is still there and not a new DSN-Less tbl.

Any suggestions or advice?

 

[dhookom]

You need to learn how to debug/troubleshoot your code. There is an FAQ in this forum that might help.

You can look at the connection information in the linked table manager, design view of the table, query of

SELECT *
FROM msysObjects

Even code like

? Currentdb.TableDefs("YourTable").Connection (maybe connect)

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Have a look here:

http://www.mvps.org/access/tables/tbl0009.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

Looks like I have some more reading to do. I appreciate the help and will repost what works so there is a record. Thanks for the help!

 

[DaveMac]

Thanks for the help one last thing now that I have done it. How would I set this as a Trusted Connection? I need to use the AD for users so I do not have to always add them.


here is what I have:

Option Explicit

Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long


Private Sub Command1_Click()

Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String

Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

DataSourceName = "TestforODBC"
DatabaseName = "Sales"
Description = "TestforODBC"
DriverPath = "C:\Winnt\System32"
LastUser = "lastuser"
Server = "Sales_SVR"
DriverName = "SQL Server"

'Create the new DSN key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)

'Set the values of the new DSN key.

lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))

'Close the new DSN key.

lResult = RegCloseKey(hKeyHandle)

'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)

End Sub

 

[vbajock]

Preferred method is trusted connections

Trusted connection assume that the user has been granted security rights in SQL Server itself based on his Windows login, you would have to get in touch with whoever admins the DB to find out if he has set up Integrated Security for the users you are targeting, in SQL Server. Whenever a new user is added, you have to set him up as "trusted" in SQL Server itself.

Untrusted connections:

Other method is risky security wise, and you have to maintain the password in your code module, but it makes it easier to distribute the app to anyone you decide should have it. For an app that is going to have widespread use, you can ask the DB to set up a single "super user" login in the SQL DB itself that only has access to the tables you require, and pass the login credentials to the DB via your code. If you go that route, make sure you set a password on your Access Modules because the password has to be hard-coded into your code that opens the connection.