By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Automate Table Creation

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.