When I was working on a project at Exxon, I had to load Access on startup to connect to a DSN.
Here is how I implemented it (I don’t remember where I found the code):
Private Sub Form_Open(Cancel As Integer)
'#####================================================================================
'##
'##
'## Purpose:
'## For use in scripts that require SQL System DSNs (setup and pre-req check)
'## 1. Checks if SQL System DSN exists if not, exits
'## 2. Creates SQL System DSN by running /Setup and prompt user for values (or uses defaults)
'##
'## Requirements:
'## 1. Must have administrative permissions to create SQL system DSN.
'## 2. Change Const DSN: DSN="Your DSN Name"
'##
'## Basic Logic:
'## 1. Checks to see if your SQL System DSN exists
'## 2. If you run the script with /Setup, script will prompt you for values and creates SQL system DSN
'#####================================================================================
Dim strMsg As String
On Error Resume Next
Const DSN = "my_intranet"
If Not DSNExists(DSN) Then
strMsg = vbNewLine & "***Failed Pre-Req. A SQL system DSN must first exist to 'RMH Intranet DB', " & _
"use /Setup to create one*** (or one will be created automatically)"
MsgBox strMsg
Setup
Wscript.Quit
End If
End Sub
Sub Setup()
'DESC: Creats a System DSN to database
Dim strDataSourceName: strDataSourceName = "my_intranet" 'InputBox("Enter Data Source Name", "Data Source Name", "MY_DATABASE_DSN")
Dim strSQLServer: strSQLServer = "192.168.1.2" 'InputBox("Enter SQL Server Name", "SQL Server", "SQL001")
Dim strDescription: strDescription = "My Intranet DB" ' InputBox("Enter Description", "Description", "DSN to MY DATABASE")
Dim strDataBaseName: strDataBaseName = "my_test" ' InputBox("Enter Database Name", "Database", "MY_DATABASE")
'Set Values
Const SystemFolder = 1
Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim strSysPath: strSysPath = objFSO.GetSpecialFolder(SystemFolder)
Dim strDriverName: strDriverName = "MySQL ODBC 3.51 Driver"
Dim strDriverPath: strDriverPath = strSysPath & "\sqlsrv32.dll"
Dim strTrustedConnection: strTrustedConnection = "Yes"
Dim strRegPath: strRegPath = "HKCU\SOFTWARE\ODBC\ODBC.INI\" & strDataSourceName & "\"
Dim objWshNetwork: Set objWshNetwork = CreateObject("WScript.Network")
Dim strLastUser: strLastUser = objWshNetwork.UserName
Dim objWshShell: Set objWshShell = CreateObject("Wscript.Shell")
'Create Key
objWshShell.RegWrite strRegPath, ""
'Create Values
objWshShell.RegWrite strRegPath & "Database", strDataBaseName
objWshShell.RegWrite strRegPath & "Description", strDescription
objWshShell.RegWrite strRegPath & "Driver", strDriverName
objWshShell.RegWrite strRegPath & "LastUser", strLastUser
objWshShell.RegWrite strRegPath & "Server", strSQLServer
objWshShell.RegWrite strRegPath & "Trusted_Connection", strTrustedConnection
objWshShell.RegWrite "HKCU\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & strDataSourceName, strDriverName
MsgBox "MySQL System DSN: " & strDataSourceName & " has been created"
Set objFSO = Nothing
Set objWshNetwork = Nothing
Set objWshShell = Nothing
Wscript.Quit
End Sub
Function DSNExists(strValueName)
Dim strComputer: strComputer = "."
Dim objReg: Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & _
"\root\default:StdRegProv")
Dim strKeyPath: strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const HKEY_CURRENT_CONFIG = &H80000005
Dim strDWValue
objReg.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strDWValue
If strDWValue <> "" Then
DSNExists = vbTrue
Else
DSNExists = vbFalse
End If
End Function
Let me know if you have any questions.
How to Delete Blank Rows in Excel Using VBA (Step-by-Step Guide)
How to Delete Blank Rows in Excel Using VBA (Step-by-Step Guide) When you work with large datasets in Excel, it’s common to end up with thousands of blank rows scattered throughout your worksheet. Maybe your data came from a mainframe export, a CSV download, or a legacy system that doesn’t format properly. Deleting all those […]
How to Copy Data from One Worksheet to Another Using VBA in Excel
Learn how to copy data between worksheets in Excel using VBA step-by-step! This quick tutorial shows how to automate data movement, save time, and boost productivity. Watch: How to Copy Data from One Worksheet to Another Using VBA Why Automate Copying Data Between Worksheets? Copying and pasting data manually can take up valuable time and […]
Learn Access VBA: Understand Tables, Queries, Forms, and Reports
Learn Access VBA: From Zero to Database Hero If you’ve ever opened Microsoft Access and wondered how all the pieces fit together — tables, queries, forms, and reports — this tutorial is made for you. In just a few minutes, you’ll understand how Access works behind the scenes and see how VBA (Visual Basic for […]
How to Fix Run Time Error 1004 in Excel
If you work with Microsoft Excel frequently, chances ar ling for a solution. Fortunately, this error is well-documented, and there are several ways to resolve it. In this article, we’ll explore the causes of run time error 1004, practical steps to fix it, and preventive measures to reduce the chances of it happening again. What […]
Support these sponsors: