I'm trying to write procedures in my desktop app to import data from a flat text file (upwards of 800,000 rows) and append it to a table. I am doing this by creating a linked table with the exact same field structure and running an append query. I'm doing all of this in VBA.
The procedure is supposed to get the file name from the user, link the table, append the records, then delete the link. The problem is the query. Often it doesn't run, throwing error 3001: invalid arguments, even though the query itself has no parameters. When it does run, it only adds the first field: ssn. I wind up with a table full of Social Security numbers and null fields.
The text file is linking correctly. I can't figure out where the rest of my data is going.
The procedure is supposed to get the file name from the user, link the table, append the records, then delete the link. The problem is the query. Often it doesn't run, throwing error 3001: invalid arguments, even though the query itself has no parameters. When it does run, it only adds the first field: ssn. I wind up with a table full of Social Security numbers and null fields.
Code:
INSERT INTO tblBenefits
(ssn, clm_id, cca_id, cct_id, bye, wk_ending, cct_post, reg, cwc,
seasonal, school, ucfe, ucx, interliab, interagent, cct_pd_amt,
cct_irs_am, cct_csiamt, cct_offset, cell38, cell39, cell40, cell41,
cell42, cell43, cell45, cell46, cell47, cell48, cell49, cell50,
cell51, cell52, cell53, cell54, cell55, cell56, cell57, cell58)
(SELECT ssn, clm_id, cca_id, cct_id, bye, wk_ending, cct_post, reg,
cwc, seasonal, school, ucfe, ucx, interliab, interagent,
cct_pd_amt, cct_irs_am, cct_csiamt, cct_offset, cell38, cell39,
cell40, cell41, cell42, cell43, cell45, cell46, cell47, cell48,
cell49, cell50, cell51, cell52, cell53, cell54, cell55, cell56,
cell57, cell58
FROM tblBenLink);The text file is linking correctly. I can't figure out where the rest of my data is going.