Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 3rd, 2011, 10:00 AM
Registered User
 
Join Date: May 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Append a large table

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
Reply With Quote
  #2 (permalink)  
Old May 3rd, 2011, 12:21 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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?
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
  #3 (permalink)  
Old May 4th, 2011, 03:03 AM
Registered User
 
Join Date: May 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Append a large table

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 :)
Reply With Quote
  #4 (permalink)  
Old May 4th, 2011, 08:55 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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

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

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
  #5 (permalink)  
Old May 20th, 2011, 01:17 PM
Registered User
Points: 38, Level: 1
Points: 38, Level: 1 Points: 38, Level: 1 Points: 38, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 6
Thanked 0 Times in 0 Posts
Default

>>fida<<

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

Bob
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract 8 Tables into 1 Large Table hewstone999 Access VBA 1 March 4th, 2008 09:19 AM
create table/append data. bpdineen Access VBA 1 January 4th, 2007 11:47 AM
Append Query to Table ! penta Access 3 February 24th, 2005 04:24 PM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM
Append Records From One Table to Another Table twsinc Access VBA 4 February 29th, 2004 03:04 PM



All times are GMT -4. The time now is 03:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.