Defining Connect Strings in INI's for EXE/Setup progs.

[Snappy2873]

Hello everyone,
This tip was recieved from a friend and I thought I might pass it on to anyone building an .EXE VB program and then assembling a setup package for it. If your location changes for your program after it goes .EXE, just change the .INI.
Here goes:

The blue text is the code you add to the global module and the form(s).

The purple text is original code used for overall functionality. It can be modified if you desire but is not needed in this INI example.

The green text is for comment purposes only!

First - Cut and paste these functions into the "globalmodule".

Option Explicit
Public Declare Function GetPrivateProfileString Lib "kernel32" _
    Alias "GetPrivateProfileStringA" _
    (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, _
    ByVal lpDefault As String, _
    ByVal lpReturnedString As String, _
    ByVal nSize As Long, _
    ByVal lpFileName As String) As Long

Function SafePath(strPath As String) As String
    SafePath = IIf(Right(strPath, 1) = "\", strPath, strPath & "\")
End Function

Public Function ReadINIKeyName(strSection$, strKey$, strFileName$) As String
    Dim strKeyValue$, lngCharacters As Long
    strKeyValue$ = String$(128, 0)
    lngCharacters = GetPrivateProfileString(strSection$, strKey$, "", strKeyValue$, 127, strFileName$)
    If lngCharacters > 0 Then
        strKeyValue$ = Mid$(strKeyValue$, 1, InStr(1, strKeyValue$, vbNullChar) - 1)
    Else
        strKeyValue$ = ""
    End If
    ReadINIKeyName = strKeyValue$
End Function

Second - cut an paste your connect string into notepad and save as an .INI file in the same folder as all your VB forms. mine looks like this:

[VALUES]
ConnectionString=Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa Password=password;Initial Catalog=YourDB;Data Source=SQLSERVER
bDebug=1

Third - Inside the "formUnload" or any other event you will need to first declare some strings and then pull in the functions you defined in the global.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    Dim nResult As String
    '
If ctrAdd = 0 Then
   If ctrProcess = 0 And ctrTransaction = 1 Then
        nResult = MsgBox("Do you really want to 
        exit without saving record?", vbYesNo, 
        "YourCompany, Inc.")
      
      If nResult = "6" Then
        Dim strSQL As String
        Dim Conn1 As New ADODB.Connection

'Define your strings for use in the following statement

        Dim strConnect As String
        Dim strINIFile As String

'Make a .ini file with the connect string and pull it in
'declare SafePath and ReadINIKeyName functions in global
'This exact syntax is used to fire your global functions and pull in the data source from the .INI file you created
'Read and understand your global functions and then you can see how this statement functions.




strINIFile = SafePath(App.Path) & "YourINIFile.ini"
strConnect = ReadINIKeyName("VALUES",
"ConnectionString", strINIFile)
Conn1.ConnectionString = strConnect
Conn1.Open

         strSQL = "delete from AnyTable where  
         AnyTable.Field = " & lngTemp + 1
            Conn1.Execute (strSQL)
            adoTransaction.Refresh
            dgPLTransaction.ReBind
            '
         MsgBox "Successfully Deleted record.",  
         vbExclamation, "YourCompany, Inc."
                Conn1.Close
                frmAnyForm.Show
      Else
         MsgBox "Deletion of record canceled", 
         vbExclamation, "YourCompany, Inc."
      End If
    '
   Else
         frmAnyForm.Show
   End If
    '
Else
         frmAnyForm.Show
         ctrAdd = 0
End If
    '
End Sub

That is it, you are now ready to modify your connect string "on the fly" without any hassle. I hope this helps because it saved me from a serious nightmare. Thanks and have a great weekend.
Snappy

 

[StewartJ]

You can let the user select their own connection string with the Datalink object. You need a project reference to Microsoft OLE DB Service Component 1.0 type Library (OLEDB32.dll). Then code something like :

Dim objDataLink As MSDASC.DataLinks
Dim conConnection As ADODB.Connection

Set objDataLink = New MSDASC.DataLinks
objDataLink.hwnd = <WindowHandle> ' Sets the parent window
Set conConnection = objDataLink.PromptNew

If conConnection Is Nothing Then ' User chose 'Cancel'
Set objDataLink = Nothing
Exit Sub
End If

' conConnection is the selected connection object.

 

[Snappy2873]

Hello Stewartj,
Thanks for the input, i will put your code in my bank. Can this data link object work concurrently with my code in that it updates the INI file on the fly through the prompt?
snappy