Make Table query in VBA

D

dhstein

I have a query. I'd like to create a command button that will allow the
users to do a "Make Table Query" using that query. What is the syntax I need
to invoke the "Make Table" I'd like the result to always be a consistent
table name also - in other words it will always create "tblMyTable1" for
example. So the other question is, do I need to delete the table tblMyTable1
before creating the new one or will the Make Table do that? Thanks for any
help you can provide.
 
A

a a r o n . k e m p f

you just need to execute a simple statement like this

select *
into myQueryTbl
from myQuery
 
G

Gina Whipp

dhstein,

You can use the below to run your code and shut off the warnings to the User
never knows the old one is being replaced with the new one...

DoCmd.SetWarnins False
DoCmd.RunSQL "SELECT tblYourTableOrQuery.YourFieldName INTO
tblNameOfYourMakeTable FROM tblYourTableOrQuery;"
DoCmd.SetWarnings True

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

I have a query. I'd like to create a command button that will allow the
users to do a "Make Table Query" using that query.
Click to expand...

Why?

MakeTable queries are VERY rarely needed; they are inefficient; they don't
allow specification of field sizes (all text fields come out 255 bytes); they
don't allow creation of indexes; they bloat the database...

What purpose does this maketable serve that cannot be served by a Select
query?
 
G

Gina Whipp

John,

Not trying to make a argument but I have one, okay one out of I don't know
how many, but I do have one. It makes a table based on zip code, longitude,
latitude and radius form a form. I actually found it faster then running a
Select query when using this particular module. The fields drawn from other
tables take on the size of the field from the Origin Table which only leaves
me one 255 text field and the balance are numbers. As for bloat, barely
negilable BUT the query is only used a few times a week and all but this one
table is on the SQL Server.

So let's not shoot them down completely...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
J

John W. Vinson

John,

Not trying to make a argument but I have one, okay one out of I don't know
how many, but I do have one. It makes a table based on zip code, longitude,
latitude and radius form a form. I actually found it faster then running a
Select query when using this particular module. The fields drawn from other
tables take on the size of the field from the Origin Table which only leaves
me one 255 text field and the balance are numbers. As for bloat, barely
negilable BUT the query is only used a few times a week and all but this one
table is on the SQL Server.

So let's not shoot them down completely...
Click to expand...

Good point, Gina. I should have tempered my remarks. My concern is that some
people - especially those with dBase or some other software experience - work
on the assumption that you must have a separate Table in order to do much of
anything with the data.
 
D

dhstein

Aaron, John, Gina,

Thanks for your responses and for the interesting "debate". Here's the
purpose which is probably pretty rare, but is very real. The user - who
requested the database for his employees to use - is a hard and fast Excel
user. He has a lot of information embedded in his Excel spreadsheets which
he now wants to compare with the data in Access. So we're going to give him
a button to do a "Make Table" and he will run a macro from Excel to bring the
new table data into his spreadsheet. It seems when you import data from
Access into Excel, you only get a choice of tables - queries is not an option.

David


Gina Whipp said:
John,

Hmmm, good point. Perhaps the best approach is to find out why they think
they need a Make Table Query!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
 
G

Gina Whipp

David,

Thank you for the feedback! And yes it is true when importing that is your
only choice but did you consider linking? Of course, those hardened Excel
Users REALLY like their spreadsheets!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

dhstein said:
Aaron, John, Gina,

Thanks for your responses and for the interesting "debate". Here's the
purpose which is probably pretty rare, but is very real. The user - who
requested the database for his employees to use - is a hard and fast Excel
user. He has a lot of information embedded in his Excel spreadsheets
which
he now wants to compare with the data in Access. So we're going to give
him
a button to do a "Make Table" and he will run a macro from Excel to bring
the
new table data into his spreadsheet. It seems when you import data from
Access into Excel, you only get a choice of tables - queries is not an
option.

David
Click to expand...
 
J

John W. Vinson

So we're going to give him
a button to do a "Make Table" and he will run a macro from Excel to bring the
new table data into his spreadsheet. It seems when you import data from
Access into Excel, you only get a choice of tables - queries is not an option.
Click to expand...

However, *EXPORTING* a query from Access into Excel is perfectly
straightforward using TransferSpreadsheet. Just turn the problem around...
 
J

John W. Vinson

David,

Thank you for the feedback! And yes it is true when importing that is your
only choice but did you consider linking? Of course, those hardened Excel
Users REALLY like their spreadsheets!
Click to expand...

It's a real pity that MS lost that lawsuit - a linked spreadsheet can no
longer be edited, which really puts a crimp in that technique!
 
D

dhstein

The user doesn't have Access - I've created a run-time version and I thought
it would be easy to give him a button to do the make table. However, I
discovered today that the run-time is read only and the make table doesn't
work. So my first choice is to do the make table myself on my system after
updating the tables. My second choice would be to find a way to make the
run-time version able to update the tables. The user only gets the form I
provide, so there's no danger of inadvertent changes (I think). So if you
have any suggestions about this, please let me know.
 
J

John W. Vinson

The user doesn't have Access - I've created a run-time version and I thought
it would be easy to give him a button to do the make table. However, I
discovered today that the run-time is read only and the make table doesn't
work. So my first choice is to do the make table myself on my system after
updating the tables. My second choice would be to find a way to make the
run-time version able to update the tables. The user only gets the form I
provide, so there's no danger of inadvertent changes (I think). So if you
have any suggestions about this, please let me know.
Click to expand...

Again... *you don't need the table at all*.

You can put code in the database using TransferSpreadsheet to export the query
into Excel. This code will work in the runtime. You're quite correct, the
runtime won't allow a maketable.

Your alternative would be to have a "scratchpad" table, empty, in the database
that you distribute. Rather than running the MakeTable query, run a delete
query to empty the scratchpad; an Append query to populate it; then export the
table to Excel (or import it from Excel, if the user is more comfortable with
that).
 
G

Gina Whipp

Runtime read only? Did you copy the file from a CD? Does the User have
permissions to the folder where the database is placed? The runtime should
not be Read Only.

On another note, consider John's solution, it looks like a real viable
option!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
D

dhstein

John, Gina,

Thanks for your replies. I'd like to see why the runtime is read only -
since that might be an easier fix since the make table is already there. But
John you say that can't work, so if that's the case then I will have to use
the Transfer Spreadsheet option as soon as I can figure out how to do that.
I have manually exported data from queries to Excel, but obviously this needs
to be invoked from a command button.
 
J

John W. Vinson

I have manually exported data from queries to Excel, but obviously this needs
to be invoked from a command button.
Click to expand...

Just see the VBA help for TransferSpreadsheet. It basically does the same
thing as the manual export; you name the query and the spreadsheet
path/filename as strings in the arguments to the method.
 
D

dhstein

Thanks John. I did find information on the transferspreadsheet, but I used
your delete and append idea which works very well. So thanks for your help,
and I'll tuck that transferspreadsheet idea away for some future use.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Query Vs. Make-table Query 10
Make Table Query 1
Pass Through Make Table 7
Access Dcount (multiple criteria) 3
Make table from recordset 2
Run 2nd query using results from 1st query 0
Make table query 2
Make table using Union Query 2

Top