Append query on linked tables via Access Runtime (1 Viewer)

[Giammarco_F]

Good morning,
I have distributed an Access application to several machines on which only Access Runtime is installed. Users of the machines need to update a linked table located within a database located on a central machine, on which MS Access full version is present. When the user on the machine tries to launch the update query, however, Access gives a runtime error.
I've tried both with the DoCmd.OpenQuery command and executing native SQL code via "db.Execute strSQL", but I still get the same error.
Please note that the Access Runtime application successfully downloads data from the central database, but fails to add records to a linked table. How can I do? Thank you!

 

[Pat Hartman]

What is the error you are getting?

The Runtime and the full version of Access don't act differently in any way that I have discovered short of the Runtime not allowing design access to any objects and not showing full menus.

When your app runs on one computer but not another, the most common problem is references. The PC's that are not working may not have a needed library installed. The error message may be irrelevant because in the missing library situation, the error is usually raised on the first VBA instruction that is not in the default library that is always loaded rather than on code that is running the code provided by the actual missing library. This complaint frequently manifests on some code that references the Date function which has nothing to do with the actual missing library.

 

[ebs17]

Tables, queries and SQL statements are managed and executed by the database engine (ACE); MS Access only plays a significant role. Full version and runtime use the same ACE.

So if you get a runtime error, there will be a specific reason. So you should look at the specific query (complete SQL statement) and the content of the error message, as well as the data used, as data type errors, index errors and violations of validity rules are also possible. Of course, the set referential integrity must also be taken into account.

 

[Giammarco_F]

What is the error you are getting?

The Runtime and the full version of Access don't act differently in any way that I have discovered short of the Runtime not allowing design access to any objects and not showing full menus.

When your app runs on one computer but not another, the most common problem is references. The PC's that are not working may not have a needed library installed. The error message may be irrelevant because in the missing library situation, the error is usually raised on the first VBA instruction that is not in the default library that is always loaded rather than on code that is running the code provided by the actual missing library. This complaint frequently manifests on some code that references the Date function which has nothing to do with the actual missing library.

Hallo Pat, thank you for your answer.
You know, Access Runtime just shows the message with "runtime error" but does not give any reference code to identify the error. The error appears when trying to perform an appending query on a linked table.
Before doing this, the database performs appending queries on local tables by using "DoCmd.OpenQuery" and everything works fine.
The problem raises only where I try to perform the query on a linked table via Access Runtime, so I don't think it is a problem of missing libraries.
In any case, how should I add references in an Access Runtime version?
I am starting to think that I can not modify linked data via Access Runtime. Do you have experience of this? Did you ever manage to modify data in a linked table using a query on Access Runtime?
Thank you!

 

[ebs17]

Did you ever manage to modify data in a linked table using a query on Access Runtime?

This is a common case, not a special situation.

Use

db.Execute strSQL, dbFailOnError
db.Execute "NameQuery", dbFailOnError

and display errors.

 

[Giammarco_F]

Tables, queries and SQL statements are managed and executed by the database engine (ACE); MS Access only plays a significant role. Full version and runtime use the same ACE.

So if you get a runtime error, there will be a specific reason. So you should look at the specific query (complete SQL statement) and the content of the error message, as well as the data used, as data type errors, index errors and violations of validity rules are also possible. Of course, the set referential integrity must also be taken into account.

Dear ebs17,
Thank you for your answer. The query works fine if I run it on a linked table by using Access Full Version (even if I modify the file extensions to .accdr), but Access Runtime crashes if I run the same query on the same linked table by using Access Runtime. So, the query seems to be right to me, and I don't know how to get error informations when I run the file with Access Runtime.
Thank you!

 

[CJ_London]

I don't know how to get error informations when I run the file with Access Runtime.

not sure of the exact code but something like

db.execute sqlstr, dbfailonerror
if err<>0 then msgbox "append failed with error " & err & ": " & err.description

 

[Giammarco_F]

Tables, queries and SQL statements are managed and executed by the database engine (ACE); MS Access only plays a significant role. Full version and runtime use the same ACE.

So if you get a runtime error, there will be a specific reason. So you should look at the specific query (complete SQL statement) and the content of the error message, as well as the data used, as data type errors, index errors and violations of validity rules are also possible. Of course, the set referential integrity must also be taken into account.

Dear ebs17,
Thank you for your answer. The query works fine if I run it on a linked table by using Access Full Version (even if I modify the file extensions to .accdr), but Access Runtime crashes if I run the same query on the same linked table by using Access Runtime. So, the query seems to be right to me, and I don't know how to get error informations when I run the file with Access Runtime.
Thank you!

 

[Giammarco_F]

Hallo,
I found that the error number is 3956: The database you are trying to open requires a newer version of Microsot Access.
This error occurs only when I try to update the tables and not to read them. Isn't that weird?

Could it be due to the fact that Access Runtime is the 2016 version, while the linked table that the query is trying to update is on a database created and managed with Access 365 Full Version?
Since it is very difficult to install Access Runtime 365 on machines, since they are not connected to the internet, how can I solve this problem?
Thank you!

 

[Giammarco_F]

Hallo,
I found that the error number is 3956: The database you are trying to open requires a newer version of Microsot Access.
This error occurs only when I try to update the tables and not to read them. Isn't that weird?

Could it be due to the fact that Access Runtime is the 2016 version, while the linked table that the query is trying to update is on a database created and managed with Access 365 Full Version?
Since it is very difficult to install Access Runtime 365 on machines, since they are not connected to the internet, how can I solve this problem?
Thank you!

I found the problem. The linked table had an "Extended date/hour" field that is not supported in Access Runtime 2016.
Thank you very very much for your suggestions!