SQL Statement "Insert Into" Help Need

**Rick Brandt** · Jan 24 '07, 08:15 PM

Re: SQL Statement "Insert Into" Help Need

Takeadoe wrote:

Dear NG - I've got a bunch of SQL statements (Insert Into "Access
Table Name") generated from another software program. They look like
this: Pasting these lines into the SQL view as they are generates a
couple of different errors (missing semicolon being one of them).
They work fine if I paste 1 line at a time. Can someone tell me what
I need to do to remedy the situation? I can't hardly paste 1 at a
time! Your help is very much appreciated.

An Access query cannot execute more than one statement.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

**chris.nebinger@gmail.com** · Jan 24 '07, 09:35 PM

Re: SQL Statement "Insert Into" Help Need

This should get you going in the right direction:

Sub RunSQL()
Const FileName = "C:\SQL.txt "
Dim intFile As Integer
Dim SQL As String
Dim Temp As String
intFile = FreeFile
Open FileName For Input As intFile
Do Until EOF(intFile)
Line Input #intFile, Temp
CurrentDb.Execu te SQL
Loop
Close #intFile
End Sub

You can't execute more than one statement, so this routine reads from
the text file line by line, and executes each line.

Chris Nebinger

Takeadoe wrote:

Dear NG - I've got a bunch of SQL statements (Insert Into "Access Table
Name") generated from another software program. They look like this:
Pasting these lines into the SQL view as they are generates a couple of
different errors (missing semicolon being one of them). They work fine
if I paste 1 line at a time. Can someone tell me what I need to do to
remedy the situation? I can't hardly paste 1 at a time! Your help is
very much appreciated.
>
Mike
>
insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusCod e,CheckStation, CheckStationFla g,CheckTimeStam p,City,county,C ountyFlag,CSID, DateOfBirth,Dee r,DeerFlag,DLIn terface,FirstNa me,HarvestDate, HarvestDateFlag ,HarvestTimeSta mp,HrvstCheckSt atus,HrvstDateS tatusCode,Inval idKillCheck,Las tName,Metal,Mid dleInitial,Perm it,PermitFlag,P rocessed,State, TimeOfCheck,Tim eOfHarvest,Weap on,WeaponFlag,Z ip,Vname,ScanGr oup)
values (5948,0,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","J ackson","0","00 005948
1",#10/08/1980#,"Doe","0" ,"RR243407","BE NJAMIN",#12/30/2006#,"0",#12/30/2006
11:05:00
AM#,"0","Valid" ,"0","SCHAAF",2 47962,"J","spec ial","0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleload er","0",45420," kinnard","ML")
insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusCod e,CheckStation, CheckStationFla g,CheckTimeStam p,City,county,C ountyFlag,CSID, DateOfBirth,Dee r,DeerFlag,DLIn terface,FirstNa me,HarvestDate, HarvestDateFlag ,HarvestTimeSta mp,HrvstCheckSt atus,HrvstDateS tatusCode,Inval idKillCheck,Las tName,Metal,Mid dleInitial,Perm it,PermitFlag,P rocessed,State, TimeOfCheck,Tim eOfHarvest,Weap on,WeaponFlag,Z ip,Vname,ScanGr oup)
values (5948,1,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
1:15:00 PM#,"Wellston", "Jackson","0"," 00005948
2",#12/26/1946#,"Antlered ","0","RQ468348 ","JAMES",# 12/30/2006#,"0",#12/30/2006
10:00:00
AM#,"0","Valid" ,"0","JOHNSON", 247961,"C","own er","0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleload er","0",45692," kinnard","ML")

**chris.nebinger@gmail.com** · Jan 24 '07, 09:45 PM

Re: SQL Statement "Insert Into" Help Need

BAH! I goofed on the subroutine. This would work better:

Sub RunSQL()
Const FileName = "C:\SQL.txt "
Dim intFile As Integer
Dim SQL As String
intFile = FreeFile
Open FileName For Input As intFile
Do Until EOF(intFile)
Line Input #intFile, SQL
CurrentDb.Execu te SQL
Loop
Close #intFile
End Sub

Chris Nebinger

On Jan 24, 2:25 pm, "chris.nebin... @gmail.com"
<chris.nebin... @gmail.comwrote :

This should get you going in the right direction:
>
Sub RunSQL()
Const FileName = "C:\SQL.txt "
Dim intFile As Integer
Dim SQL As String
Dim Temp As String
intFile = FreeFile
Open FileName For Input As intFile
Do Until EOF(intFile)
Line Input #intFile, Temp
CurrentDb.Execu te SQL
Loop
Close #intFile
End Sub
>
You can't execute more than one statement, so this routine reads from
the text file line by line, and executes each line.
>
Chris Nebinger
>
>
>
Takeadoe wrote:

Dear NG - I've got a bunch of SQL statements (Insert Into "Access Table
Name") generated from another software program. They look like this:
Pasting these lines into the SQL view as they are generates a couple of
different errors (missing semicolon being one of them). They work fine
if I paste 1 line at a time. Can someone tell me what I need to do to
remedy the situation? I can't hardly paste 1 at a time! Your help is
very much appreciated.

>

Mike

>

insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusCo de,CheckStation ,CheckStationFl ag,CheckTimeSta mp,City,county, CountyFlag,CSI D,DateOfBirth,D eer,DeerFlag,DL Interface,First Name,HarvestDat e,HarvestDateF lag,HarvestTime Stamp,HrvstChec kStatus,HrvstDa teStatusCode,In validKillCheck ,LastName,Metal ,MiddleInitial, Permit,PermitFl ag,Processed,St ate,TimeOfChec k,TimeOfHarvest ,Weapon,WeaponF lag,Zip,Vname,S canGroup)
values (5948,0,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","J ackson","0","00 005948
1",#10/08/1980#,"Doe","0" ,"RR243407","BE NJAMIN",#12/30/2006#,"0",#12/30/2006
11:05:00
AM#,"0","Valid" ,"0","SCHAAF",2 47962,"J","spec ial","0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleload er","0",45420," kinnard","ML")
insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusCo de,CheckStation ,CheckStationFl ag,CheckTimeSta mp,City,county, CountyFlag,CSI D,DateOfBirth,D eer,DeerFlag,DL Interface,First Name,HarvestDat e,HarvestDateF lag,HarvestTime Stamp,HrvstChec kStatus,HrvstDa teStatusCode,In validKillCheck ,LastName,Metal ,MiddleInitial, Permit,PermitFl ag,Processed,St ate,TimeOfChec k,TimeOfHarvest ,Weapon,WeaponF lag,Zip,Vname,S canGroup)
values (5948,1,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
1:15:00 PM#,"Wellston", "Jackson","0"," 00005948
2",#12/26/1946#,"Antlered ","0","RQ468348 ","JAMES",# 12/30/2006#,"0",#12/30/2006
10:00:00
AM#,"0","Valid" ,"0","JOHNSON", 247961,"C","own er","0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleload er","0",45692," kinnard","ML")- Hide quoted text -- Show quoted text -

**Takeadoe** · Jan 25 '07, 12:45 PM

Re: SQL Statement "Insert Into" Help Need

Chris - Thank you very much! That will be a very big help!

On Jan 24, 4:33 pm, "chris.nebin... @gmail.com"
<chris.nebin... @gmail.comwrote :

BAH! I goofed on the subroutine. This would work better:
>
Sub RunSQL()
Const FileName = "C:\SQL.txt "
Dim intFile As Integer
Dim SQL As String
intFile = FreeFile
Open FileName For Input As intFile
Do Until EOF(intFile)
Line Input #intFile, SQL
CurrentDb.Execu te SQL
Loop
Close #intFile
End Sub
>
Chris Nebinger
>
On Jan 24, 2:25 pm, "chris.nebin... @gmail.com"
>
>
>
<chris.nebin... @gmail.comwrote :

This should get you going in the right direction:

>

Sub RunSQL()
Const FileName = "C:\SQL.txt "
Dim intFile As Integer
Dim SQL As String
Dim Temp As String
intFile = FreeFile
Open FileName For Input As intFile
Do Until EOF(intFile)
Line Input #intFile, Temp
CurrentDb.Execu te SQL
Loop
Close #intFile
End Sub

>

You can't execute more than one statement, so this routine reads from
the text file line by line, and executes each line.

>

Chris Nebinger

>

Takeadoewrote:

Dear NG - I've got a bunch of SQL statements (Insert Into "Access Table
Name") generated from another software program. They look like this:
Pasting these lines into the SQL view as they are generates a couple of
different errors (missing semicolon being one of them). They work fine
if I paste 1 line at a time. Can someone tell me what I need to do to
remedy the situation? I can't hardly paste 1 at a time! Your help is
very much appreciated.

>

Mike

>

insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusC ode,CheckStatio n,CheckStationF lag,CheckTimeSt amp,City,county ,CountyFlag,C SID,DateOfBirth ,Deer,DeerFlag, DLInterface,Fir stName,HarvestD ate,HarvestDa teFlag,HarvestT imeStamp,HrvstC heckStatus,Hrvs tDateStatusCode ,InvalidKillC heck,LastName,M etal,MiddleInit ial,Permit,Perm itFlag,Processe d,State,TimeO fCheck,TimeOfHa rvest,Weapon,We aponFlag,Zip,Vn ame,ScanGroup)
values (5948,0,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","J ackson","0","00 005948
1",#10/08/1980#,"Doe","0" ,"RR243407","BE NJAMIN",#12/30/2006#,"0",#12/30/2006
11:05:00
AM#,"0","Valid" ,"0","SCHAAF",2 47962,"J","spec ial","0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleload er","0",45420," kinnard","ML")
insert into
test(BatchNo,Ba tchPgNo,BatchTr ackID,CheckCoun ty,CheckDate,Ch eckDateFlag,Che ckDateStatusC ode,CheckStatio n,CheckStationF lag,CheckTimeSt amp,City,county ,CountyFlag,C SID,DateOfBirth ,Deer,DeerFlag, DLInterface,Fir stName,HarvestD ate,HarvestDa teFlag,HarvestT imeStamp,HrvstC heckStatus,Hrvs tDateStatusCode ,InvalidKillC heck,LastName,M etal,MiddleInit ial,Permit,Perm itFlag,Processe d,State,TimeO fCheck,TimeOfHa rvest,Weapon,We aponFlag,Zip,Vn ame,ScanGroup)
values (5948,1,"4008", 40,#12/30/2006#,"0","Vali d",8,"0",#12/30/2006
1:15:00 PM#,"Wellston", "Jackson","0"," 00005948
2",#12/26/1946#,"Antlered ","0","RQ468348 ","JAMES",# 12/30/2006#,"0",#12/30/2006
10:00:00
AM#,"0","Valid" ,"0","JOHNSON", 247961,"C","own er","0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleload er","0",45692," kinnard","ML")- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

SQL Statement "Insert Into" Help Need

SQL Statement "Insert Into" Help Need

Comment

Comment

Comment

Comment