We help IT Professionals succeed at work.

Copy Access Table To Same Database

Kyle93815
Kyle93815 asked
on
162 Views
Last Modified: 2010-05-03
I need to copy a table (tbl_List) and call it (tbl_List_copy), all within the same Access database.  I need to do it from a VB6 application.

All I can locate online is code to copy the table structure, which is great, but I need the contents too.

If someone could provide any help, or a code example, it would really be great.  

I really appreciate it.

Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
(That copies the data, since you said you already have the structure)

Author

Commented:
I'm trying to use the following statement, but it won't let me:

qrySQL = INSERT INTO tbl_List_copy SELECT * FROM tbl_List



What am I doing wrong?

Author

Commented:
Ha, nevermind.  Stupid quotes.  It worked.

Author

Commented:
Strange, but I noticed that if any of the fields are blank, it doesn't copy the record.

Any way around this?

You can use:

     conn.Execute "SELECT * INTO tbl_List_copy FROM tbl_List"

in order to copy a table AND it's structure to a new table (Indexes and PK's may need to be updated manually)

You will only be able to do this once, because a second time the table will already exist.

In that case, you can either:
1. Add new data to it using the above mentioned INSERT INTO:
     conn.Execute "INSERT INTO tbl_List_copy SELECT * FROM tbl_List"

2. Or use a
     conn.Execute "DELETE * FROM tbl_List_copy"

   and then a INSERT INTO
   conn.Execute "INSERT INTO tbl_List_copy SELECT * FROM tbl_List"
 
   in order to remove old data and feed it with new data again.
*****

Or, you can delete the table with:

     conn.Execute "DROP Table tbl_List_copy"

and use another SELECT INTO
    conn.Execute "SELECT * INTO tbl_List_copy FROM tbl_List"

to copy the table and it's contents again.

You can also use a SELECT INTO

to just copy a table's structure.
  Dim CONN As New ADODB.Connection
   Dim FromRS As New ADODB.Recordset
   Dim ToCONN As New ADODB.Connection
   Dim ToRS As New ADODB.Recordset
   Dim fld As ADODB.Field

   If Not (FromRS.BOF And FromRS.EOF) Then
         FromRS.MoveFirst
         FromRS.MoveLast
         FromRS.MoveFirst
         For i = 0 To FromRS.RecordCount - 1
            ToRS.AddNew
            For Each fld In FromRS.Fields
               ToRS(fld.Name) = FromRS(fld.Name)
            Next
            ToRS.Update
            rs.MoveNext
         Next i
      End If

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.