Delayed connection between VB6 program and Microsft Access database

[aquacool]

I had made a program using VB6 with Access as database to keep my inventary. I had used the ODBC Server for connection of the program to the databse by the following command through ADODB:
Public Sub connection()
Set conn = New ADODB.connection
conn.Open "dsn=Pearl"
End Sub
Here "Pearl" is the name of the Access File which is my database.
Initially my program was running quite satisfactorily. But suddenly now, it had got extremely slow in making connection with the database. My program has a lot of tables and text boxes to show me the stock or accounts of the customers or values. So whenever I pass a query to display the stock of inventory or my customer's account, it takes unusually very long time to display the contents on the datagrid (I had used DataGrid to display tables in forms). Sometimes, the time taken is so long that the program hangs up. One thing I found was that if I passes a query in which some calculations are to be made and then resulting table was to be displayed in the datagrid, the calculations were done quite fast. What was actually delayed was the writing fo the resulting data in the table in access and then display of the resulting table into the datagrid.
Then I tried making connection with the database using the following commands:
Public Sub connection()
Set conn = New ADODB.connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\gagan\My Documents\Copy-PD-14th Mar'08\Pearl.mdb"
End Sub
Here the connection with the database, was quite fast and quick, infact like before (when I was not having any problem with ODBC server). All the calculations were done very fast. Infact, when i passes any query, the results are displayed almost instantaneously in the text boxes. But the main problem in this part is that no data/table is being displayed in the datagrid. I think there is some connection proglem between the datagrid and the tables in access, using this type of connection.
Can anyone, please tell me the reason for this or any soultion to this.

 

[ByteMyzer]

In your form's Code Module declarations, add:

[COLOR="Navy"]Dim WithEvents[/COLOR] rs [COLOR="navy"]As[/COLOR] ADODB.Recordset

Then, after the conn.Open statement, add the following lines:

[COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]New[/COLOR] ADODB.Recordset
rs.Open "SELECT * FROM MyTable", conn, adOpenStatic, adLockOptimistic

[COLOR="navy"]Set[/COLOR] MyDataGrid.DataSource = rs

 

[aquacool]

The coding which you have told me is for use in form module. While in my program, I had defined "rs" as recordset in MDI Form, by using "Public rs as new ADODB.recordset"
Any suggestions?

 

[ByteMyzer]

Yes, I suggest that you try the Visual Basic wizard to generate a form with a datagrid for a Microsoft Access database, and read the code that it generates.