473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Automate Table Creation

I have the challenge of needing to automate table creation. It doesn't end there the tables have to be created from one table that looks something like this...

Email Source
at@at.com EB
no@no.com EN
in@in.com EB
at@at.com EN

I have to create those tables based on grouped records in the Source field. So in this case i would need a table EB and another EN. Problem is that this week it is EN and EB and next week they could be MN and MB so that is variable and it needs to be the table name. I need to automate this task in either Access; Access VB: or SQL as those are the only tools available. Any assistance is appreciated.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
4 2386
"Chris via AccessMonster.com" <fo***@AccessMonster.com> wrote:
I have the challenge of needing to automate table creation. It doesn't end there the tables have to be created from one table that looks something like this...

Email Source
at@at.com EB
no@no.com EN
in@in.com EB
at@at.com EN

I have to create those tables based on grouped records in the Source field. So in this case i would need a table EB and another EN. Problem is that this week it is EN and EB and next week they could be MN and MB so that is variable and it needs to be the table name. I need to automate this task in either Access; Access VB: or SQL as those are the only tools available. Any assistance is appreciated.


See the TempTables.MDB page at my website which illustrates how to use a temporary
MDB in your app. http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #2
Try this:

1. Create a Parameter query on the SourceTable with the Parameter on the
Source field

2. Create a user form with a combobox where the RowSource is a SELECT
DISTINCT query of all the unique values for Source from the SourceTable

3. Add a button to the form, with OnClick event code which runs a make table
query based on the value selected in the combobox:

SELECT SourceTable.Email INTO tblNew
FROM SourceTable
WHERE (((SourceTable.Source)=[Forms]![frmUser].[cmbSelectSource]));

You can construct the query in code as follows to name the new table
according to the Source:

Dim strSQL as String
Dim strSourceSelected as String

strSourceSelected = Trim$(Me!cmbSource.Value)

strSQL = "SELECT SourceTable.Email INTO tbl_" & strSourceSelected & "_ " &
Now()
strSQL = strSQL & " FROM SourceTable"
strSQL = strSQL & " WHERE
(((SourceTable.Source)=[Forms]![frmUser].[cmbSelectSource]));"
DoCmd.RunSQL strSQL

--

Tony D'Ambra
Web Site: aadconsulting.com
Web Blog: accessextra.net

"Chris via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:1d******************************@AccessMonste r.com...
I have the challenge of needing to automate table creation. It doesn't end
there the tables have to be created from one table that looks something
like this...

Email Source
at@at.com EB
no@no.com EN
in@in.com EB
at@at.com EN

I have to create those tables based on grouped records in the Source
field. So in this case i would need a table EB and another EN. Problem is
that this week it is EN and EB and next week they could be MN and MB so
that is variable and it needs to be the table name. I need to automate
this task in either Access; Access VB: or SQL as those are the only tools
available. Any assistance is appreciated.

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #3
Thank You. That worked well it is also an interesting way of approaching that is there anyway to get this to run on a loop where it would just create a table for every instance on the list until it was done.

I just need a little more automation to this and that would be great.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #4
I hate to keep this thread going but I was wondering if anyone knew how to take this one step further in automation. I do have it running in the form. I would just like it to automatically create the tables for every instance on the list. Right now I select the group run the command and repeat for every instance.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: yurps | last post by:
Hello here is my html, if you click the missing image in the first column on the left, the div is shown, when clicked again the div disappears...but the bottom border disappears as well...Is there...
3
by: Jon Gross | last post by:
We have a project that will require the almost realtime (every 5 minutes) creation of user accounts and a FTP site for each of them. I would like to write a VB.Net application to do this. This...
6
by: Bernd Koehler | last post by:
Hi: I am a EE prof currently teaching an IT course. One the assignments students recently completed was designing a small MS Access Database. I have two submissions that are remarkably...
1
by: tjones8611 | last post by:
I have an application that updates a CSV file every 10 minutes. I am trying to use Access to append the information in the csv file to a table. The csv file does not append the new data, row 1 is...
25
by: MLH | last post by:
In an earlier post entitled... "A97 closes down each time I open a particular report" it has been suggested that I rebuild problematic table - one in which some corruption has occurred. I...
2
by: Jeremy | last post by:
Is anyone aware of any features in .NET or 3rd party tools that would enable the automated creation/modification of graphics (e.g., buttons with text in them). I frequently need to modify only the...
15
by: Karl | last post by:
Hi all, I regularly use FTP to place Self Extracting Zip files on the web for remote users to update their datafiles. Works very nicely. I have automated the creation of the initial zip file (...
0
by: amg | last post by:
I'm looking for a way to automate the creation of an ODBC System DSN for an Oracle driver (Oracle client 10g). Any help (scripts, pointers, sample code) will be greatly appreciated. Thanks,
9
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.