ODBC
How To Connect Access To SQL Server On Amazon Virtual Machine
In this post I am going to show you how you can connect your Access database to a Microsoft SQL Server 19 running on an Amazon Virtual Machine. Something great about the current technology is that you can have a version of something that would cost you BIGGGGGGGG Money in the past! In this case […]
How To Connect Access To MySQL
In this post I am going to show you how to connect to your MySQL web database from your Access desktop database. Here are the steps: 1. MySQL table 2. Access MySQL ODBC connector 3. Link table from Access 4. Create search form. (in part 2 of this post) I am going to use the […]
How to programmatically create an Access DSN
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
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.