Slow ADO recordsets in VB to Access97

[Joey72]

I'm upgrading an old VB3 program to VB6 and have been using similar code to that listed to open a password protected database through ODBC.
GlobalConn.Open "DSN=DATABASE.MDB;uid=;pwd=JOEY;database=DATABASE.MDB"
Set GlobalCmd.ActiveConnection = GlobalConn
SQLString = SQLstr
GlobalCmd.CommandText = Trim(SQLString)
GlobalRecSet.CursorLocation = adUseClient
GlobalRecSet.Open GlobalCmd, , adOpenKeyset,adLockOptimistic
GlobalConn.BeginTrans
etc etc

It all works fine, my problem is that the program requires different recordsets open at one time which is really affecting the speed /performance of my program. It runs pretty slow when it goes through about 100 loops to update item details to a table (taking 30 seconds to do this when the VB3 version does it in a second).
Can anyone shed some light if its the way the connections are created, the recordsets or anything like that? The equivalent code in VB3 (using DAO and no ODBC) runs like lightning, and due to the tight time contraints I can't rewrite the entire package from scratch, HELP!
Any info would be appreciated.
The old package was in VB3 pro, connecting to an Access 2.0 database using DAO.
The updated package is in VB6 pro, with Access 97 using ODBC and ADO (v2.0).

Now that you are using relatively Up-To-Date s/w it might be a good idea to change to OLEDB for Jet 3.51 this will make your application run faster than ODBC.

In addition, it may be quicker to simply make a reference to the MS Access Object Library and use the GetObject method.

In VB6 professional edition or better, you should be able to add a data connection, and get the connection string from it's property box.

Ok

H

[Tonatiuh]

Henry,

Could you write the syntaxis to open the DB by this OLEDB method?

Yes,

here is an example

Provider=Microsoft.Jet.OLEDB.3.51;
Persist Security Info=False;
User ID=Admin;
Data Source=C:\WINNT\Profiles\Administrator\Personal\Visual Studio Projects\SAMS Teach Yourself ASP 2.0\SOURCE\Chapter-10\WebSiteUsers.mdb;
Mode=Share Deny None;
Extended Properties="";
COUNTRY=0;
CP=1252;
LANGID=0x0409";
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Global Partial Bulk Ops=2

I created this using the 'Add Data Connection' command in InterDev 6.0 sp4.

Don't ask me what each option means, I'm not sure myself.

Ok

H

[Joey72]

Thanks for that HenryNB, i'll give it a try.

[Joey72]

Not much luck at the moment, but i'm persisting with Jet instead of ODBC as I am getting a better performance rate using JET.
But the amount of code I need to change needs to be minimal (as most of the application i'm writing is finished except for the time consuming Item list extraction part), the other suggestions I've seen require a fair bit of time to implement. So...

I've declared a constant ConnectionStr = "Provider=Microsoft.JET.OLEDB.3.51;Data Source=c:\Data\Data.mdb"
And i'm trying to read from the database using the following.

JConn.Open ConnectionStr
Set JCmd.ActiveConnection = JConn

SQLString = "SELECT * FROM TABLE"
JCmd.CommandText = Trim(SQLString)
JRecSet.CursorLocation = adUseClient
JRecSet.Open DriveInCmd, , adOpen, adLock
JConn.BeginTrans

If JRecSet.EOF And JRecSet.BOF Then etc etc

All works fine if the database isn't password protected.
Now I've thrown in a ', ,Password' at the end of the connection string but no luck (get an error), do any of you guys have any suggestions for this?
Where can I modify the connection string to cater for password protection and also, is there something in the way I connect to the database that is a problem (example above).
Thanks. (I'm need this bit of code so I can get the Olympic Opening Ceremony started without a hitch...just joking)

[Joey72]

Found the answer: so i'll post it for anyone else who is in the same boat as me.

declare the constant for the connection string as ConnectionStr = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Data\Data.mdb;Jet OLEDB:Database Password=MyPassword"

Then if you get an error using updatebatch to update your database, set your cursor type to adUseServer if you were using adUseClient, otherwise you'll get an error on subsequent updates.

Joey your second mistache is how you oben the recordset
Don't use a comand object for refereces a connection.
better way:

Dim cn As New ADODB.Connection, strConnect As String,rs as New ADODB.Recordset,strSQLKrit as string

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path + "\Datenbanken\MyDatabase.mdb"

With cn
.CursorLocation = adUseClient
.Open strConnect
End With

strSQLKrit="Select * from Table"

rs.open strSQLKrit, cn, adOpenKeyset, adLockOptimistic

[Joey72]

Maexchen,

Thank you very much for your tip, I've tried it out and it works even better than what I originally had.
Thanks!!!