VB6 Connection (going from 2000 to 2008)

  • I'm moving a VB6 app that has been used with SQL 2000 to SQL 2008. It connect, is functional, but very slow. Things that would take less than a second take a minute. Reading various posts it looks like a different connection string would help but I havent been able to get anything else to work. Appreciate the help, thanks.

    SQL 2000:

    Private Sub Form_Load()

    IniFileName = App.Path & "\" & "SqlServer.ini"

    Dim MyServer As String

    Dim MyValue As String * 20

    MyServer = GetPrivateProfileString("Database", "Server", _

    "No INI File Located", MyValue, _

    Len(MyValue), IniFileName)

    Set m_con = New ADODB.Connection

    Call m_con.Open("DRIVER={SQL Server};SERVER=" & _

    MyValue & ";UID=CustServ;PWD=xxxxxxxx")

  • I don’t think it’s an issue with application (however it can be). Most of the times database upgrade 2000 -> 2008 performs slow if not properly done.

    Can you please provide more information on database upgrade / migration? Who did it & what all steps you followed? How big the database is? etc.

  • I would try running the queries you've got in your application in the database directly to see how fast they go--chances are this is a query optimisation issue thanks to changes in the optimiser between SQL 2000 and 2008, not a problem with VB6 itself; you'll need to rewrite the queries.

  • I'm sure the queries are running slow. Seems like they all are. I read if I change the driver, it would run faster.

    I'm not much of a VB person, all I know is I have this connection m_con that gets opened and closed all over the place in the VB application so I have to keep it.

    Old:

    Set m_con = New ADODB.Connection

    Call m_con.Open("DRIVER={SQL Server};SERVER=" & _

    MyValue & ";UID=CustServ;PWD=xxxxxxxx")

    Trying to get to:

    Set m_con = New ADODB.Connection

    Call m_con.Open("DRIVER={SQL Server Native Client};SERVER=" & _

    MyValue & ";UID=CustServ;PWD=xxxxxxxx")

    I get an error that the driver is not found; think that is the 2008 driver? Also a lot of threads say going to that driver cured slowed stored procs, actually they got faster.

  • I figured it out. I haven't used this app in years but it is being used still today. I haven't updated anything either. I installed the latest service pack for VB6 and added the ADO Data Control 6 to the app. Presto, runs fast.

Viewing 6 posts - 1 through 5 (of 5 total)

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