Create report in Access on linked tables?
I have the ODBC connection set up and created all the relationships for the tables I need, but when I try to build the report I get the error below.
"The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in design view"
Subsequent attempts to run the report generate this error:
"Cannot join on Memo, OLE, or Hyperlink object ([Computers].[ComputerID]=[Displays].[ComputerID])"
If I make the tables local and convert the data type for the fields to Text everything works fine. It only seems to be when fields have Memo data types that I get the error. I am still of a novice when it comes to Access so hopefully someone can point out my oversight of the obvious.
EDIT: It wouldn't let me post in-line image of the errors.
Comments
We haven't tested accessing the SQLite database from MS Access. At this point doing this isn't supported.
From the looks of the two errors it would appear that you may need to stop the background service and the Inventory console (since both of these processes will have a hold on the database). As far the datatype error... all tables in SQLite will look to Access as though they have a Memo datatype. This is why you convert the type to an Access supported type.
Have you tried using the native Reports feature in PDQ Inventory? There are two types: Basic and SQL. Between these two report types you can generally pull up any info you need.
The native reports work fine, but the limitation is custom items. Everything you put in there has to be tied to a computer and I am trying to track non-computer items as well. I can make it work, but it's much easier to manage if I can create my own tables.
For example, let's say I am trying to track what user has a particular laptop as well as other mobile devices (MiFi, mobile phone, tablet, etc...). As long as that user always uses the same equipment, the custom items work fine. As soon as that user gets a new device or laptop though, I have to edit each of those fields for the new computer and remove from the old. This gets rather tedious.
I was trying to work around this by using my own Access DB with local tables and linking external tables from PDQ. I was hoping to then split the DB and provide a frontend for managers with just the reports.