Code to Appmd

[GracieLou]

Hello eveyone, I am using Access 2003 and SQL Server 2000.

I have much less hair right now. I keep pulling it out.

I am trying to append records to a table in an SQL db. the code is;

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

Set DbAny = CurrentDb()

For iLoop = 1 To 12 strTableName = "L" & Format(iLoop)
me.txt4 = strTableName
strSQL = "INSERT INTO dbo_GLYr1 ( BU, JrnlID, JrnlDt, Period, AcctNbr,
DeptID, CostComponent, ABMSAct, ProjID, Description, InvDt, VndrNbr, VndrNm,
VouchID, InvNbr, JrnlLnRef, Amt )" & _
"SELECT InFile.BU, InFile.JrnlID, InFile.JrnlDt, InFile.Period,
InFile.AcctNbr, InFile.DeptID, InFile.CostComponent, InFile.ABMSAct,
InFile.ProjID, InFile.Description, InFile.InvDt, InFile.VndrNbr,
InFile.VndrNm, InFile.VouchID, InFile.InvNbr, InFile.JrnlLnRef, InFile.Amt" &
_
"FROM InFile" & strTableName & "as InFile"

Next iLoop

The loop is because I have 12 monthly files to append.

I know it loops through because I have a text box on a form that shows the
value of strTableName. however, I am getting no data in the SQL table.

Any ideas?

Any help is appreciated.

Thanks.

 

[Tom van Stiphout]

On Wed, 18 Mar 2009 20:39:01 -0700, GracieLou

Sure. All you're currently doing is creating a string with the select
statement you want to execute, but you never actually execute it.
So after setting strSQL write:
DbAny.Execute(strSQL, dbFailOnError)

-Tom.
Microsoft Access MVP

 

[GracieLou]

Thanks Tom. Although that was a problem I still cannot get it to run.

If I use the DoAny.Execute(strSQL, dbFailOnError) included in you response I
get a compile error "Expected: =". Ideas? If I take the parenthesis out I
don't get an error but the code does the same thing. It runs with no results.

I have a table name InFile that is just blank fields thinking this was a
problem. Didn't help. Without it I get an error "cannot find the input
table or query 'Infile". I thought the last line of code would rename the
linked table L1 to Infile. is that not right?

Thaks

 

[Tom van Stiphout]

On Thu, 19 Mar 2009 06:00:11 -0700, GracieLou

Oops, indeed the parentheses are not needed:
DoAny.Execute strSQL, dbFailOnError

If it then runs without results, it means your SQL statement is
incorrect. I would debug this by setting a breakpoint at the Execute
line, and when the code stops there, in the immediate window type:
? strSQL
It will print the sql statement. Copy it to your copy buffer.
Create a new query, don't select anything, and switch to SQL view.
Paste the sql statement in. Switch to design view. Access will parse
the statement and point out any syntax problems.

-Tom.
Microsoft Access MVP