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
bbb
ccc
ddd
eee
fff
ggg
hhh
iii
jjj

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


Sponsored Links:



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 Access.zip (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.




HI,

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


dbs.TableDefs.Refresh




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.

Thanks!




Hi

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

Mike




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;


	Code:
	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.




Hi,
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
Next
----------------------
Mike




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?

Penmetsa




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:


	Code:
	  Sub OpenMyRecordset()

     Dim NewRST As Recordset

     Set NewRST = OpenRecordset("ETable")
     NewSRT.Close
   
  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




Hello,

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
FROM tblObj2DRDLs
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.

Thanks!