Discussion Locked

How do I copy an Access table with VB?

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to be able to copy the structure of an existing Access table in VB, checking to see if a couple of columns exist, if not, creating them, and saving them with a new name. Is this possible programatically?

5 replies

by alan williams

Why do you need to this? The standard approach is to have 2 tables, the first containing the static data (and the primary key), the seccond containing the column name and data for that column (and the foreign key}. Then you use a query to join the tables back again. You can add the second table multiple times using an 'Alias' for the table (ie table2 as [NewColumn])

by kargall

Yes this is all possible, but the solution is too complicated for an email. Send me the specifics.

by amartyat

Add an ADO Control with Datagrid control to access the Tables in Access Database (*.mdb) then to add and save with a new name use ccdlgcontrol to save
and sql Create Statement to create new db. If it works plz do drop a line at amartyat@gmail.com

by thydzik

from my understanding you want to check if fields exist, and if they don't you would like to create them.

to see if fields exist, use the following function:
Function fieldExists(s As String, ByRef tablename As String) As Boolean
Dim temp As String
On Error Resume Next
temp = CurrentDb.TableDefs(tablename).Fields(s).Name
If Err.Number = 0 Then
fieldExists = True
ElseIf Err.Number = 3265 Then
fieldExists = False
Else
fieldExists = False
End If
End Function

to create a field you would use something like this:
db.TableDefs(tablename).Fields.Append db.TableDefs(tablename).CreateField(param, dbText, 25)

finally, if you want to save this table as a different name, you should make a copy of the table at the start, using the method
DoCmd.CopyObject

This discussion is closed to new replies.