[SOLVED] MS Access Database

[hugo]

Hi

I would like to know if anyone can maybe help?

We are trying to connect to a MS Access database using Lazarus's IDE, not programmatically. Would this be possible to do when using ODBC?

The steps that were followed to setup the connection:
From SQLdb tab:

• Drag ODBCConnection object onto Frame
• Drag SQLTransaction object onto Frame
• Drag SQLQuery object onto Frame

In Windows 7:

• Go to Control Panel -> Administrative Tools -> Data Sources (ODBC)


• Add... -> Select .mdb / accdb -> Locate the database on computer to use with project -> Give that connection a name

Back to Lazarus's IDE

• Click on the ODBCConnection object created earlier (that was dragged onto Form)
• Go to its DatabaseName property and use the "name" that was given when configuring the ODBC in Windows 7
• Change its connected property from False to True (if no errors, then the connection was suppose to be successful)
• Select SQLTransaction object (that was dragged onto Form) -> Change its Database property to point to ODBCConnection object
• Select SQLQuery -> Change its Database property to point to ODBCConnection object
• Go to the SQL property of the SQLQuery object, press the ellipsis button and a window appears to type in a query
• Type a simple SELECT query that retrieves all the rows from a table in the database
Test the query -> Query returns no errors.[/li]

• In the same windows -> There is a green arrow that should display the rows returned from the table in the database, but it returns the following error message instead:

[Window Title]
Error

[Content]
Could not retrieve primary key metadata for table ArtCompetitionEntries using SQLPrimaryKeys. ODBC error details: LastReturnCode: SQL_ERROR; Record 1: SqlState: IM001; NativeError: 0; Message: [Microsoft][ODBC Driver Manager] Driver does not support this function;

[OK]

Screenshot is attached for clarity.

Any feedback would be appreciated.

Thank you

[LacaK]

Try set these properties in SQLQuery1:

1. set "UsePrimaryKeyAsKey" to false

if does not help, then

2. set "ParseSQL" to false

does error disappears ?

[hugo]

Hi LacaK

Thank you for your support on this problem.

Your suggestion helps in solving a bigger part of the problem, in that I could not bind the database to the table grid.

I guess that changing the settings you suggested, now makes it possible to do the binding....it works!

I do not really understand why one would need to adjust those two settings, as long as it makes it possible to deal with the GUI and the database table.

The problem that I ran into initially is that I could not preview the SQL, using the SQLQuery object's SQL property. It is still not possible, because of the exception.

Maybe this is some kind of bug that needs fixing in a future release of Lazarus, I don't know.

Once again, thank you for your suggestion. I struggled with this for almost 7 hours.

[LacaK]

If you can attach here your project (simpliefied) with your access database I can look deeper what happened when error is raised