How do I connect VB to an SQL-Database via ODBC?

[Guest_imported]

Hello out there,

I am not so experienced in VB programming and have jumped up a step from programming applications for access. And now I am trying to make an application that shall read from an SQL-database and met the brick wall.

While surfing around on the net I found this site so now I am trying this to get some help.

Some information:
-The application I am trying to make will run on several computers therefore I want to use a ini-file that tells the program witch ODBC connection to use.
-I am trying to get the program running without using the VB’s data-envirement.
-I am using VB 6.0 pro.

When I am trying to run the code below it seems that the program don’t find the ini-file and it stops when it tryes to open the database.

Someone out there with a tips of how I can get the program running?


Option Explicit

Global KvalregDB As Database
Global Const INI_NAME = "READKVALREG.INI"
Global Const INI_DATABASE = "Database"
Global Const INI_KVALREG_DSN = "Kvalreg DSN"

Public Sub AapneDatabase()
Dim DSN As String
DSN = LesIniLinje(INI_DATABASE, INI_KVALREG_DSN, "ODBC;&quot
Set KvalregDB = OpenDatabase("", False, False, DSN)
End Sub


Public Function ReadIniLine(Section As String, Variable As String, Standard As String) As String
Dim Fname As String
Fname = App.Path + "\" + INI_NAME
End Function


My READKVALREG.INI

[Database]
Kvalreg DSN=ODBC;DSN=Readkval;uid=uid;pwd=pwd;

Thanks

Gaimi
gaimi@spray.no

 

[Snappy2873]

Hello Gaimi,
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 & "\&quot
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.&quot

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.
Snappy

 

[Snappy2873]

Disregard color code, follow instructions and it should work..
snappy