Upgrading woes

[Cecilia]

So I have a Word template that looks up data in an Access database. In XP and Office 2003, it works great. But I am testing in 2010, and am having a weird, inconsistent error.

In Word 2010 (with an Access 97 backend), the following code works just fine:

Code: Select all

Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase(GetDataPath)  
Set rs = db.OpenRecordset("SELECT ADDRESS.* FROM MAIL_ADDRESS WHERE ((ID)=" & txtID.Text & ");")

If rs.RecordCount > 0 Then
    If Len(Trim(rs![NAME])) > 0 Then strAddress = rs![NAME]
    If Len(Trim(rs![MADDR])) > 0 Then strAddress = strAddress & vbCrLf & Trim(rs![MADDR])
    If Len(Trim(rs![MCITY])) > 0 Then strBankAddress = strAddress & vbCrLf & Trim(rs![MCITY])
    If Len(Trim(rs![MSTPOST])) > 0 Then strBankAddress = strAddress & ", " & Trim(rs![MSTPOST])
    If Len(Trim(rs![MZIP5])) > 0 Then strBankAddress = strAddress & " " & Trim(rs![MZIP5])
    
    Selection.TypeText strAddress
End If

Under the same conditions, the following generates error 3170, Could not find installable ISAM:

Code: Select all

Dim db As Database
Dim rst As Recordset

Set db = OpenDatabase(GetDataPath)
Set rst = db.OpenRecordset("Select Agency from tblAgencyAddresses Group By Agency;")
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
    lstAgencies.AddItem rst!Agency
    rst.MoveNext
Loop

The error seems to happen at (or somewhere after) rst.MoveLast, although I'm having difficulty nailing that down. What I do know is that it's finding the database fine (the same database as above) as well as the table.

According to MS, error 3170 has something to do with drivers not being installed properly. I highly doubt I'll be able to convince them to reinstall drivers for everyone, so I'm trying to work around this.

So, my question is, why would one work and one not work, and what can I do to make it work for both, all the time?

[HansV]

What happens if you change

Set rst = db.OpenRecordset("Select Agency from tblAgencyAddresses Group By Agency;")

to

Set rst = db.OpenRecordset("Select Agency from tblAgencyAddresses Group By Agency;", dbOpenDynaset)

[Cecilia]

What happens if you change
Set rst = db.OpenRecordset("Select Agency from tblAgencyAddresses Group By Agency;", dbOpenDynaset)

That's sort of where I started, my original code had dbOpenSnapshot, and I couldn't get past that line. dbOpenDynaset does the same, I can't get past that line. If I leave out that qualifier, I can at least get to opening the recordset.

[HansV]

What error message do you get if you add dbOpenShnapshot or dbOpenDynaset?

[Cecilia]

Runtime error 3170 could not find installable ISAM (the code executes on running a form, to fill a listbox, so it just errors on the form, not in the initialize event code, sadly)

[Cecilia]

Another hint: In the Access database, most of the tables appear in gray. The one that is generating the error (which happens to be linked into sharepoint) appears in yellow.

Does linking stuff in sharepoint change it in some way?

[HansV]

Ah - that might well be the cause! I don't have any experience with SharePoint though, I can't help you with that.

[Cecilia]

grrr. I knew eventually they'd figure out how to stop my evil plan of everyone having access to addresses lololol

Ok, I read somewhere that it should work if I switch to ADO, I know last time I was working on this you gave me some great ADO pointers (which I ended up not using), so I'm off to study that.

Thanks again, Hans!