Problem with runtime and pass through queries

[ColYoung]

My pass through queries do not work with the runtime version of access.



Full details:

We use a database to store contract details for the business which has become quite a complex program over the years. Some of the data can be linked to our accounts system via 'pass through' queries so certain forms and reports show data from both programs. The accounts system is Exchequer which is based around a Pervasive database. This all works fine on PCs with a full version of Access.

All staff that needed the database used to have the full version of Access but we are switching to Office 365. Under Office 365 the only licence that includes Access is way too expensive to install on all PCs so the plan is that everyone has Office 365 without Access and I install the runtime version of Access to run the contract management system. I know that I need to install the runtime version one down from the latest in order for it to work with Office 365 so I have used the runtime for Access 2013.

All works perfectly except that the pass through queries no longer seem to work. On the forms that used to show data from the accounts system the text box now just says #ERROR and if I try to run a report that accesses the data I get a error saying that execution has stopped due to a runtime error and the program shuts down.

I have tried the runtime simulation on my PC (using /runtime) and it works fine. We are currently using Windows 7.

What am I doing wrong?

[ItsMe]

...I have tried the runtime simulation on my PC (using /runtime) and it works fine. We are currently using Windows 7...

It sounds like a driver issue on the client. How are you connecting, ODBC? Is the vendor of the database providing the correct driver? Who is the vendor, Sage?

[ColYoung]

I'm not sure it would be a driver issue as it works fine on PCs with the full version of Access. It is connecting via ODBC and the accounts package is Exchequer.

Thanks

[rpeare]

How did you go about installing the runtime components for the 'bad' machines. Did you install the runtime components then office 365? or vice versa, it may be that 365 has it's own version of runtime and is blocking the use of the older version. Have you checked your ODBC drivers to make sure they still work? Go through your DSN and test the connection to make sure it's working. If it isn't the problem isn't the runtime installation at all and you may have to rework your DSN.

[ItsMe]

When I say, 'driver'. I am not referring to Microsoft or Access. I am referring to the Pervasive client drivers supplied to you by Exchequer to create an ODBC connection. Perhaps they offer an SDK or whatever.

[ColYoung]

How did you go about installing the runtime components for the 'bad' machines. Did you install the runtime components then office 365? or vice versa, it may be that 365 has it's own version of runtime and is blocking the use of the older version. Have you checked your ODBC drivers to make sure they still work? Go through your DSN and test the connection to make sure it's working. If it isn't the problem isn't the runtime installation at all and you may have to rework your DSN.

I installed Office 365 then Access runtime. Every other aspect of the database is working fine it's only the connection to the accounts system that fails.

I've tested the connection and it works.

[ColYoung]

When I say, 'driver'. I am not referring to Microsoft or Access. I am referring to the Pervasive client drivers supplied to you by Exchequer to create an ODBC connection. Perhaps they offer an SDK or whatever.

The drivers supplied work with every other version of Access and even connection via Excel it's only when I try to use the runtime version of Access that it fails. I have the most up to date version of the drivers.

[rpeare]

Can you run a test, just make a small test database linking one of the 'bad' tables into it, then make a form that shows all the data in that table, nothing fancy just all the fields in the detail section of the form.

Save that database and try to open it with one of your runtime only computers and see what happens. I'd try two different forms as well, one that relies on the current dsn, then make a 'test' dsn that is different than the one you have (i.e. if you're using a system dsn, make it a user dsn if it's currently a user dsn, make a system dsn and see if you can open either or both of the forms. If you can you may have some corruption in your database that you can get around by re-importing all the database objects into a new database to see if that helps.

[pbaldy]

Is there any code that tries to put a form or report into design view? That would fail with the runtime version.

[ItsMe]

I am glad to hear you have the appropriate client drivers installed on the workstations. I guess we can proceed believing a connection is possible from the client to the database. This does not jive with the runtime switch you ran on your machine. However, it would jive if there was early binding to a missing reference. Maybe the issue is not a failed connection.

[ColYoung]

Is there any code that tries to put a form or report into design view? That would fail with the runtime version.

No. The simplest example is a text box on a form that looks up a piece of data using DLookUp("stDesc1","exchStock","stStockCode='" & [txtProductCode] & "'")

This gives a #ERROR in the text box.

[ColYoung]

Can you run a test, just make a small test database linking one of the 'bad' tables into it, then make a form that shows all the data in that table, nothing fancy just all the fields in the detail section of the form.

Save that database and try to open it with one of your runtime only computers and see what happens. I'd try two different forms as well, one that relies on the current dsn, then make a 'test' dsn that is different than the one you have (i.e. if you're using a system dsn, make it a user dsn if it's currently a user dsn, make a system dsn and see if you can open either or both of the forms. If you can you may have some corruption in your database that you can get around by re-importing all the database objects into a new database to see if that helps.

I did as you suggested, creating a new db with just the pass through query in it and a form that showed all the data. I put that form as the opening form so it would run on the runtime version. When I opened it on the runtime PC I got an error message.... "ODBC... connection to 'xxxx' failed" .

[rpeare]

So when you go through administrative tools>odbc drivers and set up the DSN and test the data source you get a success on the runtime computer, but the connection fails when attempt to open a database using that same dsn?

That is really odd.

Did you try both a system dsn and a user dsn? I can't help but think this is a problem with the DSN it's the only thing that makes sense to me.

[ColYoung]

With apologies for not replying for a few days - yes, I have tried it all ways with the same results.

[warmslime]

I think the problem might be that you downloaded the Access Runtime 64-bit version while running a 32-bit version of Office. Can you check it? If that is the case download Runtime 32-bit and it should work perfectly.