Form Button Code

[Kensgracie]

Hello,

I just can’t figure this out.

I am using Access 2003 and SQL Server.

From a button on a form I am appending a number of files to an SQL db. All
tables have the same layout.

I would think the below code would do that, but apparently I am not right.
I want the code to cycle through all 52 files. I believe the error is in the
area where I reformat the date fields.

The “Select “ stmt is highlighted and I get a compile error: Expected: line
number or label or statement or end of statement.

I have the table in SQL. I have it linked. It has a RecID set in it.

Can someone please help me on this?

Dim strSQL As String
Dim InFile As String
Dim iLoop As Long
Dim DbAny As DAO.Database

Set DbAny = CurrentDb()

'Start loop at 2. The first file was exported in the step above.
For iLoop = 1 To 52
InFile = "D" & Format(iLoop)

strSQL = "INSERT INTO GLYr5 ( CoCd, AcctNbr, Amt, DocEntryDt, NetPmt FROM "
& InFile '& ""

"SELECT " & infile & ".CoCd, " & infile & "(Right([AcctNbrA],6) AS
..AcctNbr), ".Amt, " & infile & ("Mid([DocEntryDtA],5,2) & "-" &
Right([DocentryDtA],2) & "-" & Left([docentryDtA],4) AS .DocEntryDt), " &
infile & ".NetPmt "

 

[DStegon via AccessMonster.com]

well... this is all wrong

strSQL = "INSERT INTO GLYr5 ( CoCd, AcctNbr, Amt, DocEntryDt, NetPmt FROM "
& InFile '& ""

"SELECT " & infile & ".CoCd, " & infile & "(Right([AcctNbrA],6) AS
.AcctNbr), ".Amt, " & infile & ("Mid([DocEntryDtA],5,2) & "-" &
Right([DocentryDtA],2) & "-" & Left([docentryDtA],4) AS .DocEntryDt), " &
infile & ".NetPmt "

No ending ")" and the FROM should be at the end

INSERT INTO ThisThing ( blah, blah1, blah2, blah3 )
SELECT WHerever.blah, WHerever.blah1, WHerever.blah2, WHerever.blah3
FROM WHerever;

No??????

Hello,

I just can’t figure this out.

I am using Access 2003 and SQL Server.

From a button on a form I am appending a number of files to an SQL db. All
tables have the same layout.

I would think the below code would do that, but apparently I am not right.
I want the code to cycle through all 52 files. I believe the error is in the
area where I reformat the date fields.

The “Select “ stmt is highlighted and I get a compile error: Expected: line
number or label or statement or end of statement.

I have the table in SQL. I have it linked. It has a RecID set in it.

Can someone please help me on this?

Dim strSQL As String
Dim InFile As String
Dim iLoop As Long
Dim DbAny As DAO.Database

Set DbAny = CurrentDb()

'Start loop at 2. The first file was exported in the step above.
For iLoop = 1 To 52
InFile = "D" & Format(iLoop)

strSQL = "INSERT INTO GLYr5 ( CoCd, AcctNbr, Amt, DocEntryDt, NetPmt FROM "
& InFile '& ""

"SELECT " & infile & ".CoCd, " & infile & "(Right([AcctNbrA],6) AS
.AcctNbr), ".Amt, " & infile & ("Mid([DocEntryDtA],5,2) & "-" &
Right([DocentryDtA],2) & "-" & Left([docentryDtA],4) AS .DocEntryDt), " &
infile & ".NetPmt "