Run-Time error, Need Help!

I've been working on my first database program for my internship and it's almost finished finally...


Unfortunately I just did a beta test this weekend and a run time error popped up that I am unsure how to handle. Any help would be much appreciated.

The following details will probably help you:

I am using VB6.0, SP3.
I am connecting to an Access97 SR-2 Database (cuz my company is too cheap to upgrade yet)

I have the following references in the program:
Microsoft Access 8.0 Object Library
Microsoft ActiveX Data Objects 2.0 Library

I was originally using the ActiveX Data Objects 2.5 Library.

Upon startup, when I actually go to connect to the database, I'm getting the following error in my errorhandling routine. This is not on my computer but on the other three I've tried to install my program on.

#3706: Provider cannot be found. It may not be properly installed.
--



Any help would be greatly appreciated.

Thank you,
Eiredrake

Does the machine that you're running this program on have the appropriate drivers installed? And what format are you using to connect to this database?

Yes I'd imagine that it has the appropriate drivers. I'm using the Package/Deployment wizard and i've included (as far as I know) all the needed files.

There's no installation errors when it installs.

As far as the format I'm using ADODB.


Eiredrake

If you're using a DSN connection, that could be part of the problem. Try to reconfigure using a DSN-less connection, or you'll have to create the DSN on the computers when the program is installed. I always code my DSN-less connections in rather than using controls and what not, and have never had a problem, as long as the machine running the program has the correct drivers (which are easily installed). You'll also benefit from a performance increase while interacting with the database.

I'm not using a DSN they seemed like too much trouble for what they were worth.

I basically am creating recordsets dynamically with the open method and then closing them at the end of the sub.
Here's an example of how I'm doing it...
My network path is valid, I just don't want to show what it is please don't take offense.

The error checking doesn't actually handle any errors, it just lets me know what they are so I can write them down and then create code to handle them. The cstrDBConnect is a modular level constant that I use to hold my connect string.

Private Const cstrDBConnect = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=\\<<Valid Network Path>>\cOsmOs.mdb"
-------

Private Sub ATypicalRoutine()
Dim rstUserData As ADODB.Recordset
Dim strSQL As String
On Error GoTo errhandler

Set rstUserData = New ADODB.Recordset

strSQL = "SELECT * FROM [CS-RepTable] WHERE [CS-RepTable].NetworkLogin=" & Chr(34) & gudtCurrentUser.strUserName & Chr(34)
With rstUserData
.Open strSQL, cstrDBConnect, adOpenStatic, adLockReadOnly
If .RecordCount = 0 Then 'user not found
With frmNameEntry
.Show vbModal
.ZOrder 0
End With
Else 'User found
gudtCurrentUser.strUserSSN = CStr(CheckForNull(.Fields("IDNUM").Value, 2))
End If
.Close
End With
Set rstUserData = Nothing
Exit Sub
errhandler:
MsgBox "Error #" & Err.Number & " has occured." & vbCrLf & "Detected in " & "Typical Sub" & vbCrLf & vbCrLf & Err.Description, vbOKOnly & vbCritical, "Error Detected"
Err.Clear

I just decided to do a test...

I created a form with just an ADO control and a datagrid on it, connected it to a BS table in my database and created the installation package and installed it on the other computer.

Works fine on my computer... Returns all the spam records from the table, navigate through and everything just fine.


On the other computer it returns the same error.

I'm going to do the same thing again in a little bit on another machine. I'll create a BS database with some spam in a table and try it that way and see what happens...

Any ideas?

Eiredrake

Declare your constant as Public. Also, whenever i use an Access database, i use the following to create a connection:

Code:

---

Set objConnection = CreateObject("Adodb.Connection")
objConnection.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & txtPath.Text

---

Hope this helps.

Actually, it is because of a dependancy file.


When you use the ActiveX controls there is a problem with the installation of the objects on computers.

The file in question is MDAC_TYP.exe v2.1. It installs the Access Provider files v 4.0.

Because I was using Access97 as my database, I was using the 3.51 drivers. Which flipped out VB.

MDAC_TYP.EXE version 2.1 doesn't install 3.51 at all, so the program had no idea where the driver I specified at all and lost it's mind.

For those of you who come across this problem, you can fix it by changing your connection string to specify the 4.0 provider driver instead of the 3.51. It completely eliminates the problem! Yay!

More information can be gained at MSDN in the following articles.
Q238401 Q213846
Q197902 Q217754


Thank you for your help, Idover.