A few years ago I created a little help of how to connect to (any) database thru ADODB. Here it is, I hope it will help you.
To create the connection to your database we will use Data Environment:
Start Visual Basic 6.0
In
New tab choose
Data Project
In
Project-Data Project view double click on
DataEnvironment1 (DataEnvironment1)
That will open
DataProject - DataEnvironment1 (DataEnvironment1) with
DataEnvironment1
Connection1
Right-Click on
Connection1 and choose
Properties…
In
Provider tab choose
Microsotf OLE DB Provider for ODBC Drivers
Click
Next >> command button, you are in
Connection tab
Choose option
Use connection string (we will use this string later)
Click on
Build… command button
In
File Data Source tab choose
MS Access 97 Database (not sharable).dsn
(or whatever it is you use to connect to your database)
Click on
OK button
On
Login form click
OK button
Choose the path to your Database and click
OK
Click on
Test Connection command button,
you should have "Test connection succeeded" message Click OK
Copy the connection string you just build, it starts with something like;
"DSN=MS Access 97 Database;DBQ=C:\…….." and paste it to Notepad or somewhere.
Right-Click on
Connection1, choose
"Add Command"
That will add
Command1 to your view, right-click on
Command1
Choose
Properties
In
General tab, click on
SQL Statement option button, then choose
SQL Builder command button
That will open
Design: Command1 window and
Data View panel.with
Data Links
Data Environment Connections
(+) Connection1
Double-Click on
(+)Connection1, that will show you
(+) Tables
(+) Views
Where you can see all your tables and views (if you have any).
I use this tool to build all my SQL's and to check existing ones, too.
To create any SQL here, just drag any table to the upper part where your tool tip say "Drag and drop tables and columns here"
Save it and re-use it often.
To create the VB 6.0 Project connecting to your database with ADODB:
Open VB 6.0
Choose
Standard.EXE
In
Project - References… check
Microsoft ActiveX Data Objects 2.x library
(you may have other version, like 2.0)
I have my Connection and Closing to database done in the Module.
Code:
Option Explicit
Public Cn As ADODB.Connection
Public Sub OpenConnection()
Dim strCn As String
'Here goes your connection string we (you) build
'Paste it from Notepad or wherever you have it
strCn = "DSN=MS Access 97 Database;" _
& "DBQ=C:\…"
Set Cn = New ADODB.Connection
Cn.ConnectionString = strCn
Cn.CursorLocation = adUseNone
Cn.Open
'At this place you have connection to your Database
End Sub
Public Sub ExitAll()
'I close it in the Module this way because I can call it
'from any place in my program and close it properly
Cn.Close
End
End Sub
And then in my Forms I just create recordsets and play with them.
I have just ONE Connection to my database, but many recordsets.
Remember: connections are expensive, recordsets are cheap.
On my Form I have :
cboClass combo box, style = DropDown List
MSFlexGrid and cmdExit command button
Code:
'This Form populates Combo box (style = DropDown list)
'and the choice from combo box populates MSFlexGrid
Option Explicit
Dim recClass As ADODB.Recordset
Dim recInfo As ADODB.Recordset
Dim intCount As Integer
Private Sub Form_Load()
Dim strClassSql As String
Call OpenConnection 'from Module
'If I am going to use the same recordset many times
'I like to 'have it ready' in Form_Load like this:
Set recInfo = New ADODB.Recordset
recInfo.CursorType = adOpenStatic
recInfo.CursorLocation = adUseClient
recInfo.LockType = adLockReadOnly
'If it is one time use, I do it all-in-one place
strClassSql = "SELECT ClassName From Classes " _
& " ORDER BY ClassName"
Set recClass = New ADODB.Recordset
recClass.CursorType = adOpenStatic
recClass.CursorLocation = adUseClient
recClass.LockType = adLockReadOnly
recClass.Open strClassSql, Cn
'This recordset is ready to play with
cboClass.Clear
For intCount = 1 To recClass.RecordCount
cboClass.AddItem (recClass!ClassName)
recClass.MoveNext
Next intCount
cboClass.Text = cboClass.List(0)
recClass.Close
End Sub
Private Sub cboClass_Click()
Dim strSQL As String
strSQL = "SELECT BookInfo.* FROM BookInfo " _
& " WHERE (Class = '" & cboClass.Text & "')"
'choice from combo box may be recalled this way
recInfo.Open strSQL, Cn
'recInfo is ready to play with, you get the idea…
With MSFlexGrid1
.Clear
.Rows = 2
.Cols = 4
.FormatString = "One" & vbTab & "Two" & _
vbTab & "Three" & vbTab & "Four"
For intCount = 1 To recInfo.RecordCount
.AddItem (recInfo!Title) & vbTab & _
(recInfo!Author) & vbTab & (recInfo!Price) & _
vbTab & (recInfo!Name)
'(recInfo!Column1) is a shortcut, the full version is:
'recInfo.Fields("Column1").Value,
'but I don't like- too long
recInfo.MoveNext
Next intCount
.ColWidth(0) = .Width / 4.1
.ColWidth(1) = .Width / 4.1
.ColWidth(2) = .Width / 4.1
.ColWidth(3) = .Width / 4.1
.RowHeight(1) = 40
End With
recInfo.Close
End Sub
Private Sub cmdExit_Click()
Call ExitAll 'from Module
End
End Sub
Private Sub cmdUpdate_Click()
Cn,Execute "Update MyTable Set Col = 'Steve'"
End Sub
Private Sub cmdInsert_Click()
Cn,Execute "Insert Into MyTable …"
End Sub
Private Sub cmdDelete_Click()
Cn,Execute "Delete from MyTable …"
End Sub
Private Sub Form_Unload(Cancel As Integer)
'It is a good idea to do that.
'If not, you may run out of cursors in your database
Set recClass = Nothing
Set recInfo = Nothing
End Sub
Hope this helps.
---- Andy
pen strSQL, Cn
