Doesnt work on all computers

[alexanderjames]

Hi All

Strange question here and i just cant figure out what is causing this. The company i work for has about 50 clients running their one package. A few months ago i upgraded the database from sql 2000 to sql express 2008 mainly so that it can run on windows 7. All is good and it works perfectly. Except on certain sites it doesnt seem to connect to the database the only thing i changed is the connection string to cater for sql express 2008 and i have a form that is displayed should the connection not be successfull. Im struggling to find out what is causing this as i cant duplicate the scenario on my dev machine or my laptop as it works perfectly. Could it be something to do with MDAC or now WDAC that might not be installed on certain machines that could be causing this ? Below is the connection code which works fine.

        Case "SQL/EXPRESS 2008"
            On Error GoTo trap3
            Set cnnMain = New ADODB.Connection
            cnnMain.ConnectionString = "Driver={SQL Server Native Client 10.0};server=" & Trim(ServerName) & ";uid=" & Trim(UserName) & ";pwd=" & Trim(Password) & ";database=" & Trim(Database)
            cnnMain.ConnectionTimeout = Timeout
            cnnMain.CommandTimeout = 260
            
            cnnMain.Open
            On Error GoTo 0
            Exit Sub
trap3:
            Load frmServerErr
            frmServerErr.lblCap.Caption = err.Description & cnnMain.ConnectionString
            frmServerErr.Show vbModal
            End
    End Select

Im at a bit of a loss here.

Many Thanks
AJ

[si_the_geek]

What happens when it fails? (eg: what error message(s) do you get, etc)

Which version(s) of Windows does it fail on, and which version did you compile it on?

[alexanderjames]

Hi Si

Ive compiled it on Windows 7 but it faills on both win7 and xp. All that happens is that frmServerErr is displayed and whats even stranger is that lblCap.Caption is empty and this should dsiplay the error if any. i also have a combobox that when clicked should display the available database names.

Private Sub cmbDatabase_DropButtonClick()
    Static Dropped
    If Dropped = False Then
        Dropped = True
        ActiveReadServer5 "Exec sp_databases"
        While Not rs5.EOF
            cmbDatabase.AddItem rs5.Fields("Database_Name")
            rs5.MoveNext
        Wend
        rs5.Close
    End If
End Sub

This either times out or a get a runtime error. I cant tell you what the runtime error is because it works fine when i test it. In saying that, all the machines i test on have vb6 installed and it works. All i can think is that some Windows machines are missing one or other component, but have no idea what it is.

many Thanks
AJ

[DrUnicode]

Was it compiled on Win7 with SP1 installed?
Are you using MDAC 2.8?
Does it run on Win7-SP1 but not on Win7 w/o SP1?

If answer is yes to all of the above then see http://www.vbforums.com/showthread.php?t=643290

Try removing the ADO reference and use late binding. Recompile with Win7-SP1 to see if it fixes the issue.

[si_the_geek]

DrUnicode's suggestion may well fix the error, but you seem to have other issues that should be sorted out.

Trying to solve an unknown error is a nightmare, because it is guesswork at best, and you can never be sure if it has been fixed or not. As such you should try to find out what the error(s) are.

Based on your code I suspect that the starting events (such as Form_Load/_Initialise/...) of frmServerErr clear the error. To debug it, add a test error:

            On Error GoTo trap3
MsgBox 1/0

...you can then step thru it to see what is happening.

[alexanderjames]

You are right Si this is alot of guesswork and rather frustrating at best, But after reading DrUnicode's post and following the links he provided i think he has hit the nail on the head. As i am using sp1 with Win 7 and mdac 2.8 all my previouse exe's that i compiled on win Xp work like a charm. i only picked this up after upgrading to win 7 then converting to sql express 2008. Looks like im gonna have to do some reading on Late binding.

Thank a million guys.
AJ

[DrUnicode]

You may also be able to fix this by removing reference to MDAC 2.8 and adding reference to the appropriate TLB from this link:
http://support.microsoft.com/kb/2517589

Scroll down to bottom of this link and download the appropriate TLB (32bit i386 or 64bit x64)

For Visual Basic 6 developers

1. On the Project menu, click References.
2. Click to clear the Microsoft ActiveX Data Objects * Library and Microsoft ActiveX Data Objects Recordset * Library entries.
3. Click to select the Microsoft ActiveX Data Objects 6.0 BackCompat Library entry.
4. Recompile the application.

[alexanderjames]

Thank you DrUnicode

I will give this a bash recompile and post back with the result, as im sure there are many developers that will or are facing this problem.

Thank you again for all your advice.

Aj

[Jonney]

Useful info.Thanks DrUnicode & dilettante (http://www.vbforums.com/showthread.php?t=643290)