Once the user clicks on Show Data we will use a SQL statement to filter-out the data as per the drop down options selected by the user and the put them in the table below. We need some raw data and we will utilize Customer Service Dashboard sample database here.įirst we will design the structure of what all option we want to present for filtering the data, which you can see in the interface below.
#Odbc excel vba how to#
Today we will learn how to use Excel as a Database and how to use SQL statements to get what we want. Eventually they start using Access or SQL Server etc. People spend a lot of time thinking whether to use Excel as their database or not. Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123” and use this on my excel sheet, my life would be simpler. Thank you in advance.This is a guest post by Vijay, our in-house VBA Expert.
#Odbc excel vba driver#
UBF International” CSF = “Yes” Desc = “NAV_Test” Driver = “C:\WINNT\System32\codbc.dll” NType = “tcp” PPath = “C:\Program Files\Navision Financials” SName = “172.16.1.40” DriverName = “C/ODBC 32 bit” 'Create the new DSN-key lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWAREODBCODBC.INI” & DataSourceName, hKeyHandle) 'Set the values of the new DSN-key lResult = RegSetValueEx(hKeyHandle, “CN”, 0&, REG_SZ, ByVal CN, Len(CN)) lResult = RegSetValueEx(hKeyHandle, “CSF”, 0&, REG_SZ, ByVal CSF, Len(CSF)) lResult = RegSetValueEx(hKeyHandle, “Desc”, 0&, REG_SZ, ByVal Desc, Len(Desc)) lResult = RegSetValueEx(hKeyHandle, “Driver”, 0&, REG_SZ, ByVal Driver, Len(Driver)) lResult = RegSetValueEx(hKeyHandle, “NType”, 0&, REG_SZ, ByVal NType, Len(NType)) lResult = RegSetValueEx(hKeyHandle, “PPath”, 0&, REG_SZ, ByVal PPath, Len(PPath)) lResult = RegSetValueEx(hKeyHandle, “SName”, 0&, REG_SZ, ByVal SName, Len(SName)) 'Close the new DSN key lResult = RegCloseKey(hKeyHandle) lResult = RegCreateKey(HKEY_LOCAL_MACHINE, “SOFTWAREODBCODBC.INIODBC Data Sources”, hKeyHandle) lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, ByVal DriverName, Len(DriverName)) lResult = RegCloseKey(hKeyHandle) End Sub I’m not getting any errors, but unfortunately no dsn is created also.
#Odbc excel vba code#
This is my code in VBA: Option Explicit Private Const REG_SZ = 1 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 Sub CreateDSN() Dim DataSourceName As String Dim CN As String Dim CSF As String Dim Desc As String Dim Driver As String Dim NType As String Dim PPath As String Dim SName As String Dim DriverName As String Dim lResult As Long Dim hKeyHandle As Long 'Specify DSN parameters DataSourceName = “NAV_Test” CN = “010. The reports have to run on many clients and installing dsn’s on all clients would take a lot of time.
This to be able to run reports from Excel using C/ODBC. Hello all, I am trying to create a dsn from VBA.
0 kommentar(er)
