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

Typing SQL Tables in by hand, in Access

P: n/a
If I wanted to create a table by hand create table temp( etc, where do
I do it in access?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
"Alicia" <al******> wrote in message
news:d3************************** om...
If I wanted to create a table by hand create table temp( etc, where do
I do it in access?

Insert --> Query --> Design View --> View --> SQL View

Nov 13 '05 #2

P: n/a
Or try this:

Here is a form I have found helpful in speeding the process of experimenting
with SQL in MsAccess 2000. I makes the mechanics of preparing SQL-only
Queries go much faster, and reduces frustration. It will almost certainly
work in other versions as well, but I have not tried it.

When it is working, you can type SQL queries directly into the form, then
press enter twice and they will execute.

If you make an error, you will immediately be given a message that might
help you figure out what is wrong with you query. After you dismiss the
error dialog, you can go directly to re-edit the text you entered previously
and try again.

If your query is well formed it will run and display the results. If you
want to modify your query or start over again completely to do another
experiment, just edit and go again. If, instead, you want to save the query
you have just created as a permanent name, just rename "qryTmp".


Create a new form in Design view

Draw a single text box, from your toolbar that should be the button labeled
"ab|". Click it and then click-drag to make a large rectangle on your form.
Make it big enough for several long lines of text to accommodate a
significant SQL statement, but leave a little room for a button. (Mine is
below the text box, centered).

Now right-click on the textbox and select "Properties" (if it is not already

In the properties window, select the "other" tab.

in the "Name" property, change the name from whatever it is (e.g. "Text0")
to be (exactly) "tbSQL". (IMPORTANT: This kind of thing is good VBA coding
practice, anyway, but in this case it makes the cut and paste of the code
from below possible.)

Leave the Properties window open.

On your toolbar select the "Command Button" button, and drag an outline
below your text box to make a button.

In the wizard that opens up, select "Application" in the "Categories" frame,
and "Run Notepad" in the "Actions:" frame, and click "Next>"

Click the "Text:" radio button, and type something appropriate into the text
panel like "Perform SQL Query" then click "Next>"

In the "...meaningful name..." textbox type "btnSQL", then click "Finish"

Now in the form design window, RIGHT click on the button and select "Build
Event..." from the popup menu. This should bring up the Visual Basic Editor,
already opened up to the "Private Sub btnSQL_Click()" routine.

Select and delete the single line:
"Call Shell("NOTEPAD.EXE", 1)"

In it's place, paste in all the code given below. starting with "Dim qryDef
as QueryDef" and ending with the "End With" line. (That's just a

As a sanity check, Click the "Debug" menu and select "Compile
<yourDBnameHere> " item. If all is well, nothing visible should happen,
except the next time you click "Debug" the "Compile..." item should not be

Close the VBA editor window.

Close the form Design Window, and when prompted give the form a meaningful
name. I suggest frmSQL per VBA coding conventions.

That's it! Enter a valid SQL statement, and press <Enter> twice and the SQL
should be run. If you want to save it as a permanent Query, simply rename
qryTmp to whatever name you choose. You may have to click to another objects
frame and back again in the DB view to make the list of queries refresh.

There are more things you can do to prettify the form, but it is functional
as is. Things you might consider: remove or change the text label for the
text box (hints on what to do might be nice); remove record selectors, and
navigation buttons; make the border "thin" instead of sizable, remove the
scrollbars and properly size the form; change the button to picture instead
of text and select the picture used on the "run query" button; etc.

Here (Between the :::: lines) is the code to paste into the routine:

Dim qryDef As QueryDef
Dim qryName As String
Dim qry As String

qryName = "qryTmp"
qry = Me.tbSQL

With CurrentDb
For Each qryDef In .QueryDefs
If qryDef.Name = qryName Then
.QueryDefs.Delete qryName
End If
Next qryDef

' Create temporary Query
Set qryDef = .CreateQueryDef(qryName, qry)

'Open and display Recordset
DoCmd.OpenQuery qryName
End With

I hope this is useful for you.

"Alicia" <al******> wrote in message
news:d3************************** om...
If I wanted to create a table by hand create table temp( etc, where do
I do it in access?

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.