ODBC - Pushing data from MS Access 'into' FileMaker Server

[careybroph]

Long time MS Access user, short time FileMaker user. Doing my best to figure it out. But I've hit a wall. I have an MS Access database that contains data that the user needs to 'push' to a FileMaker Server database. Here is what I've done so far...

1. I've configured an ODBC connection (DataDirect 32-BIT SequeLink 5.5).

2. I've created an ODBC link within the MS Access DB that points to a table in my FileMaker Server DB.

3. I've created a query that identifies the MS Access records that need to be 'pushed' into my FileMaker Server DB.

4. I tested the query and it works fine as a 'Select' query (shows the appropriate records to be added).

5. I changed the query to an 'Append' query (to push it into FileMaker) and it auto detected the appropriate field names. Everything looks just great.

6. However... when I try to actually 'run' the query, it fails with the following 3-line message:

ODBC--insert on a linked table composition_FileMaker' failed.

[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker Driver][FileMaker]Cannot modify field(201)(#-511)

[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker Driver][FileMaker]QueryFailed (#-200)

I'm at a loss. If I export these records to another format (e.g. dbf or CSV), I can then manually import that file using FileMaker's import feature. So, why is it that I cannot 'append' directly via the ODBC???

As I said, I'm not a FileMaker expert. I'm more like a FileMaker 'Sophmore', which literally translates to 'smart fool' (I know just enough to be dangerous!). I appreciate having a place where I can learn from those who have "Been there. Done that.".

SMC

[careybroph]

So, some additional information. I was originally trying to push data from an MS Access query that drew from 3 linked MS Access tables. It was failing completely. I got to wondering if it was something about the query itself that it didn't like (although, I cannot see why the ODBC would care). So, I used the original query that pulled from multiple tables and I stuffed that data into a temporary table (MS Access). Then... I tried a very clean one-table query that pulled data from the new table (the temp table) into FileMaker and... IT WORKED. I have no idea why it would care whether the source data came from a single vs. multi-table append query, but it did.

So... I have a work around. But if someone has an explanation as to why, it would be helpful.

Thanks to those who read the post in an effort to help.

SMC