automation error when connecting to access

[griffitd]

Hi i have the following code:

Dim cn As ADODB.Connection
Dim lngOldCommitMode As Long

Set cn = New ADODB.Connection
If bUseSlash = True Then
cn.Open "OLE DB Services=-4;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\" & DBServer & "\" & DBPath & "\" & DBName & ".mdb"
Else
cn.Open "OLE DB Services=-4;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\My Documents\DG Software\LHScanning.mdb"
End If
lngOldCommitMode = cn.Properties("Jet OLEDB:Transaction Commit Mode")
cn.Properties("Jet OLEDB:Transaction Commit Mode") = 1

sSql = "UPDATE LockersLive SET Allocated = True WHERE BlockId = " & rs.Fields("BlockId") & " AND Isle = " & rs.Fields("Isle") & " AND Side = " & rs.Fields("Side") & " AND Row = " & rs.Fields("Row") & " AND Column = " & rs.Fields("Column")
cn.Execute sSql, , adExecuteNoRecords

cn.Properties("Jet OLEDB:Transaction Commit Mode") = lngOldCommitMode
cn.Close


on my windows 7 pc i get class not supported automation error. Im guessing its an ADO issue.

CAn anybody help please

thanks in advance

 

[tedsmith]

At what point does the error occur?

Does this error happen when you have only this code in a small test app or when you have other apps or processes running?
I use similar code but don't have the "OLE DB Services=-4;" statement in it and it works in win7

May have no connection but I had a similar "automation error" issue with similar code but only when I also had a web camera running!

 

[griffitd]

Hi Thanks for the reply.

I recompiled the vb app on a XP Virtual PC and it fixed the problem.

Thanks

 

[dilettante]

Your Windows 7 machine probably has SP1 and you ran afoul of the notorious (and very well known) ADO compatibility break there.

There are workarounds for this which are as well known as the problem at this late date.

An ADO application does not run on down-level operating systems after you recompile it on a computer that is running Windows 7 SP 1 or Windows Server 2008 R2 SP 1 or that has KB983246 installed

An ADO-based application that is compiled in Windows 7 SP1 or in Windows Server 2008 R2 SP1 does not run in earlier versions of Windows

Bug in Win7(2008R2) SP1 with ADO. TypeLibs not compatible with pre SP1

A Better solution for the Windows 7 SP1 ADO GUID changes

And the list goes on (and on, and on).

 

[dilettante]

Note that if you set a reference to ADO in the VB6 IDE while working on a Win7 SP1 machine you will be able to compile and run there. But you've built a time bomb that will go off when your program is deployed anywhere but a Win6.1 SP1 system.

Then we get yet another question like this.