SQL Statement "Insert Into" Help Need

[Takeadoe]

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,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,CheckDateStatusCode,CheckSta tion,CheckStationFlag,CheckTimeStamp,City,county,C ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInterfa ce,FirstName,HarvestDate,HarvestDateFlag,HarvestTi meStamp,HrvstCheckStatus,HrvstDateStatusCode,Inval idKillCheck,LastName,Metal,MiddleInitial,Permit,Pe rmitFlag,Processed,State,TimeOfCheck,TimeOfHarvest ,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,0,"4008",40,#12/30/2006#,"0","Valid",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","Jackson","0","00005948
1",#10/08/1980#,"Doe","0","RR243407","BENJAMIN",#12/30/2006#,"0",#12/30/2006
11:05:00
AM#,"0","Valid","0","SCHAAF",247962,"J","special", "0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleloader","0",45420,"kinnard","ML")
insert into
test(BatchNo,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,CheckDateStatusCode,CheckSta tion,CheckStationFlag,CheckTimeStamp,City,county,C ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInterfa ce,FirstName,HarvestDate,HarvestDateFlag,HarvestTi meStamp,HrvstCheckStatus,HrvstDateStatusCode,Inval idKillCheck,LastName,Metal,MiddleInitial,Permit,Pe rmitFlag,Processed,State,TimeOfCheck,TimeOfHarvest ,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,1,"4008",40,#12/30/2006#,"0","Valid",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","owner"," 0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleloader","0",45692,"kinnard","ML")

[Rick Brandt]

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]

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.Execute 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,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,CheckDateStatusCode,CheckSta tion,CheckStationFlag,CheckTimeStamp,City,county,C ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInterfa ce,FirstName,HarvestDate,HarvestDateFlag,HarvestTi meStamp,HrvstCheckStatus,HrvstDateStatusCode,Inval idKillCheck,LastName,Metal,MiddleInitial,Permit,Pe rmitFlag,Processed,State,TimeOfCheck,TimeOfHarvest ,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,0,"4008",40,#12/30/2006#,"0","Valid",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","Jackson","0","00005948
1",#10/08/1980#,"Doe","0","RR243407","BENJAMIN",#12/30/2006#,"0",#12/30/2006
11:05:00
AM#,"0","Valid","0","SCHAAF",247962,"J","special", "0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleloader","0",45420,"kinnard","ML")
insert into
test(BatchNo,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,CheckDateStatusCode,CheckSta tion,CheckStationFlag,CheckTimeStamp,City,county,C ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInterfa ce,FirstName,HarvestDate,HarvestDateFlag,HarvestTi meStamp,HrvstCheckStatus,HrvstDateStatusCode,Inval idKillCheck,LastName,Metal,MiddleInitial,Permit,Pe rmitFlag,Processed,State,TimeOfCheck,TimeOfHarvest ,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,1,"4008",40,#12/30/2006#,"0","Valid",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","owner"," 0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleloader","0",45692,"kinnard","ML")

[chris.nebinger]

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.Execute SQL
Loop
Close #intFile
End Sub
Chris Nebinger

On Jan 24, 2:25 pm, "[e-mail]"
<[e-mail]:

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.Execute 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,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,Che*ckDateStatusCode,CheckSt ation,CheckStationFlag,CheckTimeStamp,City,county, C*ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInter face,FirstName,HarvestDate,*HarvestDateFlag,Harves tTimeStamp,HrvstCheckStatus,HrvstDateStatusCode,In val*idKillCheck,LastName,Metal,MiddleInitial,Permi t,PermitFlag,Processed,State,*TimeOfCheck,TimeOfHa rvest,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,0,"4008",40,#12/30/2006#,"0","Valid",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","Jackson","0","00005948
1",#10/08/1980#,"Doe","0","RR243407","BENJAMIN",#12/30/2006#,"0",#12/30/200*6
11:05:00
AM#,"0","Valid","0","SCHAAF",247962,"J","special", "0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleloader","0",45420,"kinnard","ML")
insert into
test(BatchNo,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,Che*ckDateStatusCode,CheckSt ation,CheckStationFlag,CheckTimeStamp,City,county, C*ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInter face,FirstName,HarvestDate,*HarvestDateFlag,Harves tTimeStamp,HrvstCheckStatus,HrvstDateStatusCode,In val*idKillCheck,LastName,Metal,MiddleInitial,Permi t,PermitFlag,Processed,State,*TimeOfCheck,TimeOfHa rvest,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,1,"4008",40,#12/30/2006#,"0","Valid",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/2*006
10:00:00
AM#,"0","Valid","0","JOHNSON",247961,"C","owner"," 0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleloader","0",45692,"kinnard","ML")- Hide quoted text -- Show quoted text -

[Takeadoe]

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

On Jan 24, 4:33 pm, "[e-mail]"
<[e-mail]:

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.Execute SQL
Loop
Close #intFile
End Sub

Chris Nebinger

On Jan 24, 2:25 pm, "[e-mail]"

<[e-mail]:

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.Execute 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,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,Che**ckDateStatusCode,CheckS tation,CheckStationFlag,CheckTimeStamp,City,county ,*C*ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInt erface,FirstName,HarvestDat*e,*HarvestDateFlag,Har vestTimeStamp,HrvstCheckStatus,HrvstDateStatusCode ,In*val*idKillCheck,LastName,Metal,MiddleInitial,P ermit,PermitFlag,Processed,St*ate,*TimeOfCheck,Tim eOfHarvest,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,0,"4008",40,#12/30/2006#,"0","Valid",8,"0",#12/30/2006
2:20:00 PM#,"Dayton","Jackson","0","00005948
1",#10/08/1980#,"Doe","0","RR243407","BENJAMIN",#12/30/2006#,"0",#12/30/200**6
11:05:00
AM#,"0","Valid","0","SCHAAF",247962,"J","special", "0",#01/17/2007
12:24:03 PM#,"OH",#2:20 PM#,#11:05
AM#,"Muzzleloader","0",45420,"kinnard","ML")
insert into
test(BatchNo,BatchPgNo,BatchTrackID,CheckCounty,Ch eckDate,CheckDateFlag,Che**ckDateStatusCode,CheckS tation,CheckStationFlag,CheckTimeStamp,City,county ,*C*ountyFlag,CSID,DateOfBirth,Deer,DeerFlag,DLInt erface,FirstName,HarvestDat*e,*HarvestDateFlag,Har vestTimeStamp,HrvstCheckStatus,HrvstDateStatusCode ,In*val*idKillCheck,LastName,Metal,MiddleInitial,P ermit,PermitFlag,Processed,St*ate,*TimeOfCheck,Tim eOfHarvest,Weapon,WeaponFlag,Zip,Vname,ScanGroup)
values (5948,1,"4008",40,#12/30/2006#,"0","Valid",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/2**006
10:00:00
AM#,"0","Valid","0","JOHNSON",247961,"C","owner"," 0",#01/17/2007
12:24:03 PM#,"OH",#1:15 PM#,#10:00
AM#,"Muzzleloader","0",45692,"kinnard","ML")- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -