Access to SQL - Speeding up SQL

Question

Access to SQL - Speeding up SQL

coyote-602892

Grasshopper

Points: 14
More actions
July 2, 2008 at 11:52 am

#188903

I had an app that runs quite fast VB6 with an Access Back End. I have converted the App to VB6 with SQL Express as the Back End. Much, much slower. I think the problem is the sql update, I use a With statement to specify the SQL command and that re-opens the connection to the database. With Access, I opened the database once at the beginning and would use the recordset.execute SQLCommand. Worked great. Seems like there is nothing like that now, I have to use something like Recordset.Open SQLCommand,ConnectionString, etc.
Is there something like Recordset.Requery (but where I can specify a different SQL Statement, not just refresh the data?)
Thanks,
Michael.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

I've not used VB6 against a SQL database for a while, but you can't use refresh to process a different query.

I used to open a connection at the beginning of an app. This would stay open. I'd then create command objects as and when I needed to make a request against the database.

Are your queries hard coded in the application or are you calling stored procedures from the database?

You definately want to be calling stored procedures.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

With Access, Opening a connection is fast, but corruption is a concern, so you open & close for each action/transaction.

With SQL Server, Opening a connection (relatively) slow, however, corruption is not a concern, so you open a connection on App startup and just keep it open.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

coyote-602892 Grasshopper Points: 14 More actions · Answer 3

thanks for the comments. i may have found my answer with the object.Execute command. which allows you to submit a new query without reopening the connection. (I hope). Will test today. No, I am not using stored procedures because the sql content is actually created on the fly from data tables, and is more of a conversion utility than an application.