Option Explicit
' These constant variables are used in loading the DSN Names
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1
' Load DSN Names from the ODBC.dll file
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As _
Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As _
Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal _
cbDescriptionMax As Integer, pcbDescription As Integer) As Integer
Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (env As Long) As _
Integer
Private Sub cboDSN_Click()
Call LoadCboTables 'Load the Tables to the cboTables control after a DSN is selected
End Sub
Private Sub cboTables_Click()
' Check to see if the user selected both a DSN and a Table
If (cboDSN.Text <> "(None)") And (cboTables.Text <> "(None)") Then
cmdOK.Enabled = True
Else
cmdOK.Enabled = False
End If
End Sub
Private Sub cmdCancel_Click()
' When exit application, form will unload and program will end
Unload frmDSNLogin
End
End Sub
Private Sub cmdOK_Click()
' Creating a fMainForm (Form Object) using frmMain
Dim fMainForm As frmMain
Set fMainForm = New frmMain
frmDSNLogin.MousePointer = vbHourglass
fMainForm.DSN_Name = cboDSN.Text 'Assign cboDSN.Text into a variable (DSN_Name) for frmMain
fMainForm.Table_Name = cboTables.Text 'Assign cboTables.Text into a variable (Table_Name) for frmMain
'fMainForm.Form_LoadMe
frmChoice.Show
End Sub
Private Sub Form_Load()
Call LoadCboDsn 'Load the cboDSN box with DSN values from the function
End Sub
Private Sub LoadCboDsn() 'Function to retrieve and load DSNs into the cboDSN
On Error Resume Next
Const iDsnMaxLen As Integer = 1024
Const iDrvMaxLen As Integer = 1024
Dim iRslt As Integer
Dim sDSNItem As String * iDsnMaxLen
Dim sDRVItem As String * iDrvMaxLen
Dim sDSN As String
Dim iDSNLen As Integer
Dim iDRVLen As Integer
Dim lHenv As Long 'handle to the environment
'get the DSNs
If (SQLAllocEnv(lHenv) <> -1) Then
Do
sDSNItem = Space$(iDsnMaxLen)
iRslt = SQLDataSources(lHenv, SQL_FETCH_NEXT, _
sDSNItem, iDsnMaxLen, iDSNLen, _
sDRVItem, iDrvMaxLen, iDRVLen)
sDSN = Left$(sDSNItem, iDSNLen)
'Check to see if it is a system DSN, if so, do not add to cboDSN
If (sDSN <> "MS Access Database") And (sDSN <> "dBASE Files") And (sDSN <> "Excel Files") And (sDSN <> "Visual FoxPro Database") And (sDSN <> "Visual FoxPro Tables") And (sDSN <> "dBase Files - Word") And (sDSN <> "FoxPro Files - Word") And (sDSN <> "FoodMart 2000") And (sDSN <> "CROR8V36") And (sDSN <> "CRSS") And (sDSN <> "CRXMLV36") And (sDSN <> "Xtreme Sample Database") And (sDSN <> "CE8") And (sDSN <> "TikkisDb") And (sDSN <> "MQIS") Then
If (sDSN <> Space$(iDSNLen)) Then
cboDSN.AddItem sDSN
End If
End If
Loop Until (iRslt <> SQL_SUCCESS)
End If
End Sub
Private Sub LoadCboTables() 'Function to retrieve and load tables into the cboTables
Dim adoConn As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set adoConn = New ADODB.Connection
strCnn = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & cboDSN.Text & ";"
adoConn.Open strCnn
Set rstSchema = adoConn.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
' Only load tables, into the cboTables, that have "Processed" within the name
If (InStr(rstSchema!Table_Name, "Processed")) Then
cboTables.AddItem (rstSchema!Table_Name)
End If
rstSchema.MoveNext
Loop
rstSchema.Close
adoConn.Close
End Sub
