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

%3CLINGO-SUB%20id%3D%22lingo-sub-1390784%22%20slang%3D%22en-US%22%3EHelp%20creating%20an%20Access%20pass-through%20query%20to%20insert%20data%20into%20an%20SQL%20Server%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1390784%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20valid%20pass-through%20query%20that%20works%20and%20creates%20the%20record%20in%20the%20SQL%20server%20table%2C%20but%20it%20is%20one-off%20and%20I%20would%20have%20to%20manually%20change%20some%20field%20values%20each%20time%20and%20re-run%20to%20add%20a%20bunch%20of%20records.%20Sometimes%20my%20program%20has%20to%20add%202%20or%203%20but%20sometimes%20a%20few%20hundred.%20Therefore%20I%20need%20to%20do%20this%20in%20code%2C%20cycling%20through%20the%20source%20table%20in%20Access%2C%20getting%20part%20of%20the%20data%20I%20need.%20I%20have%20the%20code%20running%20to%20cycle%20through%20the%20code%2C%20testing%20it%20with%20debug.print%2C%20and%20I%20think%20I%20have%20the%20SQL%20string%20built%20correctly%2C%20but%20DoCmd.RunSQL%20%22mySQLString%22%20gives%20the%20following%20error%3A%20%22Invalid%20SQL%20statement%3B%20expected%20'DELETE'%2C%20'INSERT'%2C%20'PROCEDURE%2C%20'SELECT'%2C%20or%20'UPDATE'.%20Here%20is%20my%20SQL%20string.%3C%2FP%3E%3CP%3EINSERT%20INTO%20Attendance%20(Attendance%2C%20Employee%2C%20Work_Date%2C%20Regular_Minutes%2C%20Attendance_Type%2C%20Lock_Times%2C%20Source%2C%20Last_Updated)%20VALUES%20(NewID()%2C%20'PERKR'%2C%20'12%2F28%2F2020'%2C600%2C2%2C-1%2C0%2C'05%2F14%2F2020')%3B%3C%2FP%3E%3CP%3EMy%20references%20are%20set%20to%20ADODB%20instead%20of%20DAO.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3Erobtperk%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1390784%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%20Pass-through%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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.

 

Thanks,

robtperk
 

0 Replies