Append a large table

fida · May 3rd, 2011, 10:00 AM

Hello!

I have large table that has 59 fields (table name: new_mbook).
I have another larger table that has more than 69 fields called current.
I want to append current to new_mbook.
I tried writing the append code unfortunately I got an error which said to many lines of code.

* Please note that I didn't write all the fields inside the query.
I only listed them under the declaration

I would really appreciate anyone's assistance.
Thanks in advance :)

Code:

Function AppendTable() As Boolean
'toTableName As String, frmTableName As String, _
'      FieldName As String, FieldName2 As String) As Boolean

   ' Append To a Table toTableName from another table frmTableName .
   ' Accepts
   ' toTableName: Name of table to Append to
   ' frmTableName: Name of table to Append from
   ' FieldName: Name of the field to Append
   ' FieldName2: Name of the second field to Append
   
   ' State the values of the parameters
  toTableName = "new_mbook"
   frmTableName = "tblNamn"
  FieldName = "TERM_CODE_KEY"
  ' FieldName2 = "TERM_DESC"
  ' FieldName3 = "ID"
  ' FieldName4 = "LAST_NAME"
  ' FieldName5 = "FIRST_NAME"
  ' FieldName6 = "MIDDLE_NAME"
  ' FieldName7 = "MIDDLE_INITIAL"
  ' FieldName8 = "NAME_PREFIX"
  ' FieldName9 = "BIRTH_DATE"
  ' FieldName10 = "AGE"
  ' FieldName11 = "GENDER"
  ' FieldName12 = "LGCY_DESC"
  ' FieldName13 = "STREET1_LINE1"
  ' FieldName14 = "CITY1"
  ' FieldName15 = "NATN_DESC1"
  ' FieldName16 = "PHONE_AREA_CODE1"
  ' FieldName17 = "PHONE_NUMBER1"
 '  FieldName18 = "PHONE_TYPE1"
 '  FieldName19 = "ADMIT_TERM"
 '  FieldName20 = "CATALOG_TERM"
 '  FieldName21 = "LEVL_CODE"
 '  FieldName22 = "LEVL_DESC"
 '  FieldName23 = "STYP_CODE"
 '  FieldName24 = "RESD_DESC"
 '  FieldName25 = "PROGRAM_1"
 '  FieldName26 = "COLL_CODE"
 '  FieldName27 = "COLL_DESC"
 '  FieldName28 = "DEGC_CODE"
 '  FieldName29 = "DEGC_DESC"
 '  FieldName30 = "MAJR_CODE1"
 '  FieldName31 = "MAJR_DESC1"
 '  FieldName32 = "MAJR_CONC1"
 '  FieldName33 = "MAJR_MINR1"
 '  FieldName34 = "MINR1_DESC"
 '  FieldName35 = "CLAS_CODE"
 '  FieldName36 = "CLAS_DESC"
 '  FieldName37 = "TOTAL_CREDIT_HOURS"
 '  FieldName38 = "ASTD_CODE_END_OF_TERM"
 '  FieldName39 = "ASTD_DESC_END_OF_TERM"
 '  FieldName40 = "STVNATN_CODE"
 '  FieldName41 = "1"
 '  FieldName42 = "2"
 '  FieldName43 = "3"
 '  FieldName44 = "4"
 '  FieldName45 = "5"
 '  FieldName46 = "6"
   FieldName47 = "7"
 '  FieldName48 = "8"
   FieldName49 = "STVNATN_NATION"
   FieldName50 = "SWBENRS_EMAIL"
   FieldName51 = "SWBENRS_ROOM"
   FieldName52 = "LAST_TERM_REG"
   FieldName53 = "ADVR_ID"
   FieldName54 = "ADVR_LAST_NAME"
   FieldName55 = "ADVR_FIRST_NAME"
   FieldName56 = "ADMIT_CODE"
   FieldName57 = "ADMIT_DESC"
   FieldName58 = "DEPT_CODE"
   FieldName59 = "Adviser"
 
    
   ' Returns True on success, false otherwise
   USAGE: AppendTable "toTableName", "frmTableName", "FieldName", "FieldName2"
   On Error GoTo errhandler
  Dim strSql As String, Db As DAO.Database


   'Create the Append Insert Into Select SQL Code from our Four String Values

   strSql = "INSERT INTO " & toTableName & "(" & FieldName & ", " & FieldName2 & ")" & _
          " SELECT " & "[" & frmTableName & "]." & FieldName & ",[" & frmTableName & "]." & FieldName2 & _
         " FROM " & frmTableName & ";"


   Print the SQL so we can paste into the query build if there are errors
   Debug.Print strSql
  Use Current Database
   Set Db = CurrentDb()


   Run the SQL Query
   Db.Execute strSql

   If no errors return true
  AppendTable = True
ExitHere:

   Set Db = Nothing

   Notify the user the process is complete.
   MsgBox "Insert Into Select Complete"
   Exit Function
errhandler:
  There is an error return false
  AppendTable = False
  With Err
     MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "AppendTable"
   End With
   Resume ExitHere
End Function

gbianchi · May 3rd, 2011, 12:21 PM

If access accepts 256 fields on a table, there is now way an sql throws you that error. Or you read the error bad, or the problem is on another part...

Maybe you can be more explicit on what's happening?

fida · May 4th, 2011, 03:03 AM

Hello!

The error that I was getting was too many line continuations, so I removed them as much as possible and it works.

However, I have another problem which is regarding sql queries.
I hope someone may be of assistance :)

Is it possible to append 2 tables and each has a different number of fields.
example the table I want to append to (new_mbook) has 59 fields; however the table I want to append from (current) has more than 59.
I do NOT want to append ALL the fields from current, I want to append specific fields.

This is how I'm doing it (simplified version):

Code:

strSql = "INSERT INTO " & toTableName & "(" & FieldName & ", " & FieldName2 & ")" & _
" SELECT " & "[" & frmTableName & "]." & FieldName & ",[" & frmTableName & "]." & FieldName2 & _
" FROM " & frmTableName & ";"

Please note that the FieldNames have the same names in both tables.
(In both new_mbook and current the field names are the same for example CLAS_DESC)

Thank you for your help :)

gbianchi · May 4th, 2011, 08:55 AM

Yes, not only it possible, it also need to be done that way ;).

If you don't find a problem running it, why ask? ;)

amerifax · May 20th, 2011, 01:17 PM

>>fida<<

Is this a one time condition or do you repeat this procedure frequent, weekly etc.

Bob