[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15661
  • Last Modified:

Create ODBC Connection through batch file

Hello Experts:

Currently I have an access db that runs the macro to create odbc connection to connect to sql server and below is the code that I use. I need to create odbc connection through the batch (bat) file. I need help writing that bat file.
Thank you for all your help.

here is the current for the module in access:

Option Compare Database
Option Explicit
Option Base 1

Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_CURRENT_USER As Long = &H80000001

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

Public Function Add_User_DSN()

'Open Þ Load Þ Resize Þ Activate Þ Current

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 AddSetup As String
Dim Connect As String

Dim lResult As Long
Dim hKeyHandle As Long
   
   'Specify the DSN parameters.

   DataSourceName = "test-ODBC"
   DatabaseName = "test"
   Description = "SUI Application Connection to SQL DB"
   DriverPath = "C:\WINDOWS\system32"
   'LastUser = "sa"
   Server = "example"
   DriverName = "SQL Server"
   AddSetup = "No"

   'Create the new DSN key.

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

   'Set the values of the new DSN key.

   'lResult = RegSetValueEx(hKeyHandle, "QuotedId", 0&, REG_SZ, _
      'ByVal AddSetup, Len(AddSetup))
   'lResult = RegSetValueEx(hKeyHandle, "AnsiNPW", 0&, REG_SZ, _
      'ByVal AddSetup, Len(AddSetup))
   'lResult = RegSetValueEx(hKeyHandle, "AutoTranslate", 0&, REG_SZ, _
      'ByVal AddSetup, Len(AddSetup))
   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))
    lResult = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
      ByVal Connect, Len(Connect))
   
   '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_CURRENT_USER, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)

End Function
0
proudpaki9
Asked:
proudpaki9
  • 2
1 Solution
 
cjohnsen2Commented:
0
 
Bradley HaynesCommented:
Here is an example you can modify with your environment specifics.

Batch file to run the macro
Batch file: DailySalesExport.bat
REM This runs the macro that exports the reports to the J drive
PATH = "C:\Program Files\Microsoft Office\Office\;C:\Windows\Command"
START /WAIT Msaccess.exe "D:\GD SAM BACKUP\GDSAM.mdb" /x backup
0
 
proudpaki9Author Commented:
cjohnsen2:
How can I modify it to work only for 1 server?

Thanks
0
 
Bradley HaynesCommented:
Change this "D:\GD SAM BACKUP\GDSAM.mdb"  path to your .mdb file.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now