VBA Code Problem (using office 2007 no problem but with Office 2010 does not work)

[dimitri_international]

I have a code error that was fine with Windows 7 office 32 bit, but with Windows 8.1 Office 64 bit.. it keep marking error at the set db = opendatabase command! how do i fix this problem. or how do i rewrite the code so it works!


---------

Sub AddNewProduct()
Dim dbsDuke As Database
Dim rstDuke As Recordset
'--- This Routine uses the "Microsoft DAO 2.5/3.5 Compatibility Library"
' Libraries are Referenced by selecting "Tools", "References" on the toolbar
GetPath
Load UserForm3
Set dbsDuke = OpenDatabase(MDBPath & "DukeMfg.mdb", , True)
Set rstDuke = dbsDuke.OpenRecordset("SELECT * FROM PartCat;", dbOpenDynaset, dbReadOnly)
With rstDuke
.MoveFirst
Do
S = ""
If Not IsNull(!PartCatNum) Then
S = Trim(!PartCatNum) & " - "
End If
If Not IsNull(!PartCatDescr) Then
S = S & Trim(!PartCatDescr)
End If
If Not IsNull(!PartCatComments) Then
S = S & ": " & Trim(!PartCatComments)
End If
UserForm3.ComboBox1.AddItem S
.MoveNext
Loop Until .EOF
End With
rstDuke.Close
dbsDuke.Close
UserForm3.ComboBox1.ListIndex = 0
UserForm3.Show
End Sub

 

[baitmaster]

welcome to the board

I replied to this thread yesterday but the website appears to have had some issues and lots of stuff has disappeared, so I'll post again

DAO doesn't work with 64-bit systems; I don't recall the exact reasons, but RoryA posted an explanation yesterday which has also disappeared. Anyway, I recommend you swap to ADODB instead of DAO. There's a free download available at Case studies & example files - Spreadsheet Wizard Ltd (see last example) which contains most of the elements you'll need, let me know if you have any questions