Problems with VBA SQL Insert into

  • Thread starter kilgorq via AccessMonster.com
  • Start date
K

kilgorq via AccessMonster.com

This one does not work. It gives me a syntax error in the INSERT INTO
Expression

INSERT INTO BoardTrackingArchive ( Tracking_ID, Board_ID, Board_Fail_ID,
Rework_ID, Board_Part_ID, Part_ID, Date, DateStepClosed, SFC, Status,
Employee, Step_Active, Failure_Description, Failure_Disposition,
Confirmed_Fix, Rework_Disposition, UnitCost, Debug_Time, Rework_Time,
Live_Board ) SELECT tbl_Tracking.Tracking_ID, tbl_Tracking.Board_ID,
tbl_Tracking.Board_Fail_ID, tbl_Tracking.Rework_ID, tbl_Tracking.
Board_Part_ID, tbl_Tracking.Part_ID, tbl_Tracking.Date, tbl_Tracking.
DateStepClosed, tbl_Tracking.SFC, tbl_Tracking.Status, tbl_Tracking.Employee,
tbl_Tracking.Step_Active, tbl_Tracking.Failure_Description, tbl_Tracking.
Failure_Disposition, tbl_Tracking.Confirmed_Fix, tbl_Tracking.
Rework_Disposition, tbl_Tracking.UnitCost, tbl_Tracking.Debug_Time,
tbl_Tracking.Rework_Time, tbl_Tracking.Live_Board FROM tbl_Tracking WHERE
tbl_Tracking.Board_ID = 21;

This one Works Fine.

INSERT INTO BoardFailArchive ( Board_Fail_ID, Board_ID, System_ID, Rework_ID,
Part_ID, Fail_Start_Date, Fail_End_Date, Serial, Employee, NC_Code, NC_Child,
RC_Code, Description, Disposition, Debug_Time, Confirmed_Fix, Rework_Time,
Rework_Instance_Counter, Fail_Instance_Counter, Tech_Comments ) SELECT
tbl_Board_Fail.Board_Fail_ID, tbl_Board_Fail.Board_ID, tbl_Board_Fail.
System_ID, tbl_Board_Fail.Rework_ID, tbl_Board_Fail.Part_ID, tbl_Board_Fail.
Fail_Start_Date, tbl_Board_Fail.Fail_End_Date, tbl_Board_Fail.Serial,
tbl_Board_Fail.Employee, tbl_Board_Fail.NC_Code, tbl_Board_Fail.NC_Child,
tbl_Board_Fail.RC_Code, tbl_Board_Fail.Description, tbl_Board_Fail.
Disposition, tbl_Board_Fail.Debug_Time, tbl_Board_Fail.Confirmed_Fix,
tbl_Board_Fail.Rework_Time, tbl_Board_Fail.Rework_Instance_Counter,
tbl_Board_Fail.Fail_Instance_Counter, tbl_Board_Fail.Tech_Comments FROM
tbl_Board_Fail WHERE tbl_Board_Fail.Board_ID = 21;



I am working with 6 table and queries. 3 queries work and 3 don’t. The syntax
is the same on all of them.



Here is the code I use.



Function fnSourceTable(strSourceTable)

Set db = CurrentDb()

Set tdf = db.TableDefs(strSourceTable)

For Each fld In tdf.Fields

strSourceField = strSourceField & strSourceTable & "." & fld.Name & ",
"

Next

strSourceField = Left(strSourceField, Len(strSourceField) - 2)

End Function



Function fnDestinationTable(strDestinationTable)

strDestinationField = " ("

Set db = CurrentDb()

Set tdf = db.TableDefs(strDestinationTable)

For Each fld In tdf.Fields

strDestinationField = strDestinationField & " " & fld.Name & ","

Next

strDestinationField = Left(strDestinationField, Len(strDestinationField) -
1)

End Function



Function fnArchiveBoard(strArchiveMode)

fnArchiveBoard = True



Select Case strArchiveMode

Case "Tracking": strSourceTable = "tbl_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

Case "Fail": strSourceTable = "tbl_Board_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

Case "Rework": strSourceTable = "tbl_Board_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

Case "Part": strSourceTable = "tbl_Board_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

Case "Correlation": strSourceTable = "tbl_Board_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

Case "RMTT": strSourceTable = "tbl_Board_" & strArchiveMode:
strDestinationTable = "Board" & strArchiveMode & "Archive"

End Select



If fnAppendRecord = False Then: fnArchiveBoard = False: Exit Function

If fnDeleteRecord = False Then: fnArchiveBoard = False: Exit Function



End Function

Function fnAppendRecord()

On Error GoTo Err_fnAppendRecord

fnAppendRecord = True

'Copy Records

fnSourceTable (strSourceTable)

fnDestinationTable (strDestinationTable)



strBoardID = Me.Board_ID



'Define SQL statement for action query.



strSQLAppend = "INSERT INTO " & strDestinationTable & strDestinationField
& " ) " & _

"SELECT " & strSourceField & " " & _

"FROM " & strSourceTable & " " & _

"WHERE " & strSourceTable & ".Board_ID = " & strBoardID & ";"



CurrentDb.Execute strSQLAppend, dbFailOnError



'Clear Variables

strDestinationTable = ""

strDestinationField = ""



Exit_fnAppendRecord:

Exit Function



Err_fnAppendRecord:

fnMailError (strSQLAppend)

fnAppendRecord = False

Resume Exit_fnAppendRecord



End Function

Function fnDeleteRecord()

On Error GoTo Err_fnDeleteRecord

fnDeleteRecord = True

'Delete Records

fnSourceTable (strSourceTable)

strBoardID = Me.Board_ID



'Define SQL statement for action query.



strSQLDelete = "DELETE " & strSourceField & " " & _

"FROM " & strSourceTable & " " & _

"WHERE " & strSourceTable & ".Board_ID = " & strBoardID & ";"



CurrentDb.Execute strSQLDelete, dbFailOnError



'Clear Variables

strSourceTable = ""

strSourceField = ""



Exit_fnDeleteRecord:

Exit Function



Err_fnDeleteRecord:

fnMailError (strSQLDelete)

fnDeleteRecord = False

Resume Exit_fnDeleteRecord



End Function



Private Sub cmdArchiveBoard_Click()

If fnArchiveBoard("Tracking") = False Then: MsgBox "ERROR Tracking
Archive incomplete": Exit Sub

If fnArchiveBoard("Fail") = False Then: MsgBox "ERROR Fail Archive
incomplete": Exit Sub

If fnArchiveBoard("Rework") = False Then: MsgBox "ERROR Rework Archive
incomplete": Exit Sub

If fnArchiveBoard("Part") = False Then: MsgBox "ERROR Part Archive
incomplete": Exit Sub

If fnArchiveBoard("Correlation") = False Then: MsgBox " ERROR Correlation
Archive incomplete": Exit Sub

If fnArchiveBoard("RMTT") = False Then: MsgBox "ERROR RMTT Archive
incomplete": Exit Sub



'Set Flags on board table.

Me.DateArchived = Now()

Me.Requery

End Sub
 
J

John Spencer

Date is a reserved word and should either be surrounded by brackets or
you need to add the table name to reference it - BoardTrackingArchive.Date

INSERT INTO BoardTrackingArchive (
Tracking_ID, Board_ID, Board_Fail_ID
, Rework_ID, Board_Part_ID, Part_ID
, [Date], DateStepClosed, SFC <<<<< This line changed >>>>>>>
, Status, Employee, Step_Active
, Failure_Description, Failure_Disposition, Confirmed_Fix
, Rework_Disposition, UnitCost, Debug_Time
, Rework_Time, Live_Board )

SELECT tbl_Tracking.Tracking_ID, tbl_Tracking.Board_ID,
tbl_Tracking.Board_Fail_ID

, tbl_Tracking.Rework_ID, tbl_Tracking.Board_Part_ID, tbl_Tracking.Part_ID

, tbl_Tracking.[Date], tbl_Tracking.DateStepClosed, tbl_Tracking.SFC

, tbl_Tracking.Status, tbl_Tracking.Employee, tbl_Tracking.Step_Active

, tbl_Tracking.Failure_Description, tbl_Tracking.Failure_Disposition,
tbl_Tracking.Confirmed_Fix

, tbl_Tracking.Rework_Disposition, tbl_Tracking.UnitCost,
tbl_Tracking.Debug_Time

, tbl_Tracking.Rework_Time, tbl_Tracking.Live_Board

FROM tbl_Tracking WHERE
tbl_Tracking.Board_ID = 21;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
K

kilgorq via AccessMonster.com

Thanks You That did the trick. My fix was

Function fnDestinationTable(strDestinationTable)
strDestinationField = " ("
Set db = CurrentDb()
Set tdf = db.TableDefs(strDestinationTable)
For Each fld In tdf.Fields
strDestinationField = strDestinationField & " [" & fld.Name & "]," I
ADDED BRACKETS TO THIS LINE
Next
strDestinationField = Left(strDestinationField, Len(strDestinationField) -
1)
strDestination = strDestinationTable & strDestinationField & " ) "
End Function

John said:
Date is a reserved word and should either be surrounded by brackets or
you need to add the table name to reference it - BoardTrackingArchive.Date

INSERT INTO BoardTrackingArchive (
Tracking_ID, Board_ID, Board_Fail_ID
, Rework_ID, Board_Part_ID, Part_ID
, [Date], DateStepClosed, SFC <<<<< This line changed >>>>>>>
, Status, Employee, Step_Active
, Failure_Description, Failure_Disposition, Confirmed_Fix
, Rework_Disposition, UnitCost, Debug_Time
, Rework_Time, Live_Board )

SELECT tbl_Tracking.Tracking_ID, tbl_Tracking.Board_ID,
tbl_Tracking.Board_Fail_ID

, tbl_Tracking.Rework_ID, tbl_Tracking.Board_Part_ID, tbl_Tracking.Part_ID

, tbl_Tracking.[Date], tbl_Tracking.DateStepClosed, tbl_Tracking.SFC

, tbl_Tracking.Status, tbl_Tracking.Employee, tbl_Tracking.Step_Active

, tbl_Tracking.Failure_Description, tbl_Tracking.Failure_Disposition,
tbl_Tracking.Confirmed_Fix

, tbl_Tracking.Rework_Disposition, tbl_Tracking.UnitCost,
tbl_Tracking.Debug_Time

, tbl_Tracking.Rework_Time, tbl_Tracking.Live_Board

FROM tbl_Tracking WHERE
tbl_Tracking.Board_ID = 21;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
This one does not work. It gives me a syntax error in the INSERT INTO
Expression
Click to expand...
[quoted text clipped - 268 lines]
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Archiving with VBA 1
Problem with Excel VBA 0
Print with VBA 4
Passing values to an SQL Server backend database using a form 1
Connection Recordset Loop Problem with VBA - Help 2
Need Help with Inserting in SQL 6
Shellexecute fails with Excel 0
Problems with "All" option in combo box 4

Top