Writing to an Access File with ODBC and SQL

[jimkent]

I am trying to update tables within a MS Access DB. I do it using the Execute SQL script command. I can't seem to make the statement move from the FM table to the Access table. The command I'm using is the following:

INSERT INTO WebOrders ( F1, F2, F3, F4 ) IN LunchRecords

SELECT Orders.OrderID, Orders.StudentID, Orders.OrderDate, Orders.DeliveryWeek

FROM Orders;

Where WebOrders is the MSAccess table, F1 - F4 is the field names LunchRecords is the ODBC name and after the SELECT are all the fields that I want to take from the FM DB to the Access DB. The problem is, that the fields (after the SELECT statement) are not being transfered to the Access DB. What is the correct call? Not sure should it be FileMaker.FP5.StudentID or something like that? The name of the filemaker file? That can't be. humm..

[Reed]

Check out this example from the Filemaker help:

Example

The following example of a manual SQL statement entry executes once each time you run the Execute SQL script step

INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, WorkPhone, Salary) VALUES (100, 'Joe', 'Smith', 'Software Engineer', '987-7000', 100000)

UPDATE Employees SET Title = 'Manager' WHERE EmployeeID = 103

DELETE FROM Employees WHERE EmployeeID = 103

The following example of a field-based SQL statement shows a calculation field that strings together text with a field that can be used to insert or update a number of records in the found set

"INSERT INTO Employees (EmpID, LastName) Values (" & EmpID & "," & "'" & Last Name & )"

With EmpID of 103 and Lastname of "Smith" the calculation of the field above is translated to

INSERT INTO Employees (EmpID, LastName) Values (103,'Smith')

[jimkent]

Yeah but these statements don't seem to be going outside the FM DB. There is no SELECT or ODBC name in which it is going to go. I think these are too simplified for my case.

Thanks.

[transpower]

Try using MSQUERY with both FM and Access open and ODBC enabled, with all permissions set.

[jimkent]

Typical Microsoft. They convenently removed MSQUERY from OFFICE 2003.

[transpower]

Are you sure? Try this:

Word 2003 | View | Toolbars | Database | Insert Database | Get Data | Tools | MS Query | then do your SQL