Help creating an Access pass-through query to insert data into an SQL Server table

Copper Contributor

I have a valid pass-through query that works and creates the record in the SQL server table, but it is one-off and I would have to manually change some field values each time and re-run to add a bunch of records. Sometimes my program has to add 2 or 3 but sometimes a few hundred. Therefore I need to do this in code, cycling through the source table in Access, getting part of the data I need. I have the code running to cycle through the code, testing it with debug.print, and I think I have the SQL string built correctly, but DoCmd.RunSQL "mySQLString" gives the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE, 'SELECT', or 'UPDATE'. Here is my SQL string.

INSERT INTO Attendance (Attendance, Employee, Work_Date, Regular_Minutes, Attendance_Type, Lock_Times, Source, Last_Updated) VALUES (NewID(), 'PERKR', '12/28/2020',600,2,-1,0,'05/14/2020');

My references are set to ADODB instead of DAO.




0 Replies