Using VBA create a new table from an existing table

Using VBA I need to create a new table and move data from a single column in an existing table to (10) separate fields in the new table.
Existing table: aaa

New table: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 (Field names)
aaa bbb ccc ddd eee fff ggg hhh iii jjj

How can I do this. TIA

Post your answer or comment

comments powered by Disqus
I would like to create a new database from an existing data base with the same fields but I don't want the data. Can anyone give me a step-by step instruction. I also want to duplicate the form as well.
I am currently using ACCESS 2000

Hi Friends,

I am new to vba and access and is working on Populating the data of an excel sheet into an excess table.

Here is the Scenario.

I have created a databse named "Access_Converted" in a shared drive and in this database I have created a table called "Excel_to_Access".
Every month there comes a new version of this table, so what i want is that the entire structure of this "Excel_to_Access" table is copied to a new table. The name of this table is given by the user via inputbox on running the code.
I tried but i am not able to create a copy of the existing table.
Please refer the excel sheet and the Access_Converted database in the attachments.

Please help me guyz. Attached Files (32.2 KB, 2 views) Reply With Quote 03-26-2013, 07:57 AM #2 rpeare VIP Windows XP Access 2003 Join Date Jul 2011 Posts 1,917 Are you saying you do not know what the structure of your excel table is going to be from month to month and you want to be able to have something that will import the data into a table that is built to mimic your spreadsheet every month?

Or is is something simpler. If your excel spreadsheet the same from month to month and you just want to import the data into an existing table.


I have a running application which I created in excel.
Each time a new copy of the excel sheet is worked with, I need to create a new table in an access database, that holds part of the data coming from excel.

Can you explain, or can you point me to a manual that I can read that explains how to do this ?

Try to create a new table in an external database using the following code
but when I run the code nothing happens

any idea whats missing here (using access 2003)

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Dim tName As String
Dim test As String
Dim strDB As String

'Hide the Access Window

Set dbs = OpenDatabase("c:program filescompair service managementSQSV2DATA.mdb")

'Insert the name of the table you wish to create
tName = "TblCheckList"

Set tdf = dbs.CreateTableDef(tName)

' Create table wih Description fields.
Set fld = tdf.CreateField("checkid", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("code", dbText, 2)
tdf.Fields.Append fld
Set fld = tdf.CreateField("modeltype", dbText, 7)
tdf.Fields.Append fld
Set fld = tdf.CreateField("item", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv1", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv2", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv3", dbText, 10)
tdf.Fields.Append fld
Set fld = tdf.CreateField("serv4", dbText, 10)
tdf.Fields.Append fld


I'm a newby to VBA. I have tried to create a new table from within a module following the code in the help file. I get a compile error "User-defined type not defined" to the code Dim dbs As Database. Where should I be defining what?

Hi guys
Pls how do I create a new query using an existing one but just changing a couple of conditions. It really should be simple but the copy option doesn't seem to do it.

Hi, I want to know how to create a new table from 4 fields entered in a form. The reason I'm doing this, because I'm creating a report that goes through numerous macros to come up with the results. And in the beginning I'm creating a table that is use in all my queries. But not knowing how to do any Visual Basic or not knowing a lot about Access, need some help completing this.

My 4 fields are description, route #, blk length and waste factor.

In the form I have the end user enter the description, which is unique, then the last 3 fields populate on the form. I want to have a command button that creates a table for these 4 fields. Then all my queries will run in the macro I've already created, giving me my final report.



I have created a new recordset in my VBA code using a query (i.e. set rs = dbcurr.openrecordset(sQuery) - where sQuery is the query string).

is the any way of saving the reultant recordset as a new table?

Thanks for your help


I am trying to create a new table using a macro however everything i run the function it tells me that the table can't be locked because it is already in use. I added a command to close the form the macro is launched from because the table is used on that form but I still get the same error.

I am creating the table using do.cmd RunSQL

This is the function I am using to create the new table;

	Function CreateTable()

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT qryTotalPartsUsed.PartNumber,  qryTotalPartsUsed.Ordered INTO tblOnOrder FROM qryTotalPartsUsed"
DoCmd.RunSQL "CREATE INDEX PartNumber ON  tblOnOrder(PartNumber) With  Primary"
DoCmd.SetWarnings True
End Function

Any ideas on how I can get around this?

I need to create a new table using three existing tables. Calculations are maded.

I was able to use the SELECT INTO statement for two tables but can't do it for three.

I keep getting syntax errors. I created the new table using query design tool and copied SQL but it would not work in VB.

SELECT Case_123_Contingency.[Contingency Name], ([Case_456_Contingency]![Congestion Cost (k$)]-[Case_123_Contingency]![Congestion Cost (k$)])/1000 AS Expr1, ([Case_456_Contingency]![Congestion Cost (k$)]-[Case_123_Contingency]![Congestion Cost (k$)])/1000*[Contingency Ownership]![Computed CAISO Ownership (%)] AS Expr2

FROM (Case_123_Contingency INNER JOIN Case_456_Contingency ON Case_123_Contingency.[Contingency Name] = Case_456_Contingency.[Contingency Name]) INNER JOIN [Contingency Ownership] ON Case_123_Contingency.[Contingency Name] = [Contingency Ownership].[Contingency Name]

I tried it using where statemnet also.

I'm trying to create a new table from another existing two in vba. I'm using this code without success... Any help will be gratefully taken.
Dim strSQL as string
Dim tablaSuma As DAO.TableDef
Dim rst As DAO.Recordset

strSQL = "SELECT * FROM Necesidades_TRS1, Pedidos WHERE Pedidos.Código=Necesidades_TRS1.Código"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set tablaSuma = CurrentDb.CreateTableDef("TablaSum")
For Each Campo In rst.Fields
tablaSuma.CreateField(Campo.Name, DB_SINGLE) = Campo

Good Morning to All,

I hope someone can give me a hand here!

I am trying to do an “automatic” Version Update on my backend DB. I am using the following code to create a new Table. My only problem is that I can’t seem to set the primary key. It does create a unique index for “RID” but doesn’t have the little key symbol next to it! Why not?
And the next problem is how to create a “double key” eg. I have a table where the key should be a combination of Staff_ID and Form_ID

Set td = db.CreateTableDef("Reihentbl")
Set fld = td.CreateField("RID", dbLong)
fld.Required = True
fld.Attributes = dbAutoIncrField
td.Fields.Append fld
Set idx = td.CreateIndex("RID") 'Primary key
idx.Unique = True
Set fld = idx.CreateField("RID", dbLong)
idx.Fields.Append fld
td.Indexes.Append idx
Set fld = td.CreateField("Reihe", dbText, 5)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
Set fld = td.CreateField("RBezeichnung", dbText, 100)
fld.Required = False
fld.AllowZeroLength = True
td.Fields.Append fld
Set fld = td.CreateField("BFID", dbLong)
td.Fields.Append fld
db.TableDefs.Append td

I have a problem.

I have a query in my database.
I have to create a new table with new fields (not the fields in the query).
This new table is like a summary table of the query. How do we create a new table with new fields?

And can we "count" the number of records in the query and display the number in the new table under a new field using any worksheet expression?

And can this table be brought out as a fine report?

Please help me with the procedure or any code required?


Hi again

I would like to create a New Table [NTable] based on a Existing Table [ETable]. I can do so with a MAKE_Table Query but I would like to do so in CODE - to minimize the amount of Queries and to understand the use of code and the commands/methods involved.

I did try to look in the help file and found the use of a method called OpenRecordset but I'm not sure if is the right one to use and it gives me the error "Can't find project or library" when used (Error msg higtihgt the word "OpenRecordset")

Here is what I've been trying:

	  Sub OpenMyRecordset()

     Dim NewRST As Recordset

     Set NewRST = OpenRecordset("ETable")
  End Sub

I am really not sure how to do it. First I would just like to create a new table/recordset that is the same as the old one, just to see if my syntax is correct and then try to add a filter.

Say ETable have four fields (Name, Surname, Number and BirthDate)

Just to understand the syntax, I would like your help on creating the new Table [NTable] so that it only contains the fields Name, Surname and Number of only all those entries with Names starting with say "B"

I would greatly appreciate your help

Regards WR


I am wondering if there is anyone who can help with some vb for a command button, that when pressed will create a new table with a few pre-set columns?

Also, can the new table be named after the [Job] field (4 number name) from the record in view?

Please I hope someone can help! I have researched for hours but I cant find anything that seems to work.

I am stuck with a problem where I need to create a new table from an excel file. In detail my problem is;
The excel file contains an order from a customer, like below,
BOM Component Qty
123 abc 2
123 def 2
234 ert 1
234 qwe 1
234 uio 1

I need to create a table from this data like;
Qty Partno
1 123
4 abc
4 def
1 234
2 ert
2 qwe
2 uio

NB! In reality the components are numbers not letters.
The qty for BOM is always 1 and the component 2xqty.

It is important that the BOM number is listed first and thereafter its components.

So I need to somehow associate the BOM number with its components and list them together as a group.
I just cannot figure out how to do this.

ANy help is greatly appreciated.

Is it possible to update a field in a SQL table from an update query in MS Access which uses MS Access table? Have the following SQL syntax & gives me error can't find linked tblObj2DRDLs (Access) table:

UPDATE Client_Episode
SET Client_Episode.Has_RE = "Y"
SELECT Agency_Code, Client_Number, Date_Of_Admission
INNER JOIN Client_Episode on
(tblObj2DRDLs.Agency_Code = Client_Episode.Agency) and
(tblObj2DRDLs.Client_Number = Client_Episode.Client_Number) and
(tblObj2DRDLs.Date_Of_Admission =Client_Episode.ADMN_Date)
WHERE tblObj2DRDLs.Service_Center_Type_Service="RE";

Hi access goeroe's

Is there some clever person who can help me out with following:

I have a table with e.g. followin entries:

Param Value
Action Z1
begda 01012005
endda 31122005

Now from this table I would like to create a new table with

Action begda endda
Z1 01012005 31122005

Is this possible in any way ?

Thx for all your help

How do I create a daily table from monthly data? I have a monthly table and want to split it into a daily table by dividing each monthly value by the number of days in that month. I need this so I can compare the new daily values to other daily values.


I wasn't quite sure where to ask this so I'm posting this in the general area first.

I have a table of notes by matter number. Each matter number is unique and could have more than one note assigned to it so there could be 5 notes (5 records) for the same matter number.

Is there a way to create a new table that lists the matter number in one field and in the other field, ALL of the notes from the original table (all in one field, separated by commas, etc.).

It is almost like concatenating or adding together a bunch of fields (colunns) in ONE RECORD but what I want to do is add a bunch of records (rows) together to get one record that contains ALL of the notes in one field and the matter number in the other field.

I might also need to narrow it down to the date when the note was recorded.

If you have any ideas on how to do this, I would really appreciate your help.

Thanks so much!

I have never used Access. I am the entire IT department at my job and I am trying to learn how to use Access in order to be able to keep our Jobs system updated. I have Access 2013.

How do I create a new Table that locates all of these functions and then how do I have my Front End access it and Display it nicely?

This is exactly what I need to do:

I have a Front End and a Back End - All of the numbers and calculations I need to access are all within the Access file already in different areas. I need to create a new report, it will be called something like Job Cost Breakdown.

The report will show:
[Total Sales] [Total Costs] [Cost of Goods Sold] [Gross Profit] [Commissions Earned] [Net Profit]

These are all basically functions of each other
*Total Sales and Total Costs are accessible somewhere in the Database and are already reporting on Job Reports.
*Cost of Goods Sold is simply Total Costs / Total Sales (displayed as a %)
*Gross Profit is Total Sales - Total Costs
*Commissions Earned is a calculation based off commission rates found unique to each sales rep (already in the database on the commissions report)
*Net Profit is the Gross Profit - Commissions Earned.

Is this not enough info? As I said I have never used Access so I am not sure exactly what information needs to be provided.

Thank you!

I have a table with 5 fields. I am trying to make a new table with a make table query. In the old table there is a field called 'code'. We are changing coding structure and reason codes so I am trying a calculated field called 'new codes' in the new table. What I would like is to somewhere in the query say that if code is "MT" or "NT" or "FC" in the field code then it will be a "MT" in the calculated 'new codes' field.

So my question is how and where do I tell it to evaluate the old code field?

IN Access 2000 I want to compose an SQL statement to create a new table from an existing one. I want to select a datetime field from the existing table and insert it as a text string in the new table. Is there some kind of SQL conversion function I can use in the SELECT statement to achieve this?

Something like:
SELECT C2STR(datefld, "mm/dd/yy") AS txtdate INTO tblNew FROM tblOld;

Greetings all,
I have a table that shows a contract number in one field and it has a field called competing contracts. The competing contracts field can be null or it can have one or many other contract numbers in it. If it has many contract numbers then they are seperated by semicolons. if there is only on the semicolon is not there. I am attempting to create a new table with the first contract number and then the next field would hold one competing contract number. So there would be multiple instances of the original contract number and only one instance of each competing contract number. I am coding only in VB. Below is the code I have used thusfar. The source file is from another company so I have no control over the column as it is sent to me.

Public Sub Competing()
Dim strMaster As String
Dim strCompetitors As String
Dim varCompetitors As Variant
Dim i As Integer
With CurrentDb.OpenRecordset("File Import")
If Not .BOF Then
Do While Not .EOF 'For each record in File Import...
strMaster = ![Master Contract Number]
If Len(Nz(![Related Contracts], "")) 0 Then 'If there are related contracts...
varCompetitors = Split(![Related Contracts], ";") 'Split the related contracts into an array
For i = 0 To UBound(varCompetitors) 'Parse through the array adding each related contract to the table
CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) " & _
"SELECT " & strMaster & ", " & varCompetitors(i) & ";"
Next i
End If
End If
End With
End Sub

This code was suggested from another forum and seems much better than what I was trying originally. However, I am getting an error with this code as well.

Run Time Error '3075'
Syntax Error (missing operator) in query expression 'PP-OR-017A'

The error relates to the following line in the code.

CurrentDb.Execute "INSERT INTO Competing_Contract_List ( [System Contract], [Competing Contract] ) " & _
"SELECT " & strMaster & " , " & varCompetitors(i) & " ;"

If anyone can help me figure out what might be wrong in this code I would apprieciate it.


Not finding an answer? Try a Google search.