Multiple values for Insert Into

[Claudette Hennessy]

I tried to create a States lookup table by using variations of

INSERT INTO states (name,abbrev) Values ( Alabama', 'AL');
INSERT INTO states (name,abbrev) Values ( 'Arkansas', 'AK');

from a code example on the web which works in MYSQL, etc. Finally I copied
the information from the PO webpage, loaded it into an Excel table, and
imported the table into Access, taking about 5 minutes total.

Is there a more elegant way of inserting data which is not already in a
table?
Just curious,
Claudette Hennessy

 

[Tom van Stiphout]

On Sat, 14 Feb 2009 10:46:37 -0500, "Claudette Hennessy"

"name" is a reserved word, so you have to use [name]. Or rename the
field to StateName.
You can simply open the table and start typing away.
If you want to import programmatically there are other options but
they only work if the data is already available somewhere else. For
example you could have attached your Excel file as a table, and run an
Append query.

-Tom.
Microsoft Access MVP

 

[ken]

Claudette:

You don't describe the context in which you need to insert the rows
into the States table, but a common requirement for this sort of thing
is when you have a combo box on a form bound to another table, e.g. a
Cities table, so that you can simply select the state (usually the
abbreviation, which would be a foreign key column in the Cities table,
and to which the combo box would be bound) from a drop down list. In
that situation, if the state is not already in the States table, you
can simply type the abbreviation into the combo box and, after
confirming, a States form will open with the abbreviation already in
place ready for you to enter the state name. Closing the States form
then returns you to the Cities form. This done by code in the combo
box's NotInList event procedure as follows:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmStates", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmStates closed
DoCmd.Close acForm, "frmStates"
' ensure state has been added
If Not IsNull(DLookup("abbrev", "States", "abbrev = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to States table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

In the frmStates form's open event procedure you'd put the following
code to set the DefaultValue property of the abbrev control to the
value you entered in the combo box on and passed to the frmStates form
as its OpenArgs property:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.abbrev.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

 

[Claudette Hennessy]

Thank you, Ken and Tom, I guess I wasn't clear; the issue was converting
MySQL code into a form that Access would accept. I wanted to create a
States table to use in a combo box, without having to type in all the
states. I found on the web


# usps_states_list.sql # # This will create and then populate a MySQL table
with a list of the names and # USPS abbreviations for US states and
possessions in existence....

CREATE TABLE IF NOT EXISTS states ( id INT NOT NULL auto_increment, name
CHAR(40) NOT NULL, abbrev CHAR(2) NOT NULL, PRIMARY KEY (id) );
INSERT INTO states VALUES (NULL, 'Alaska', 'AK');
INSERT INTO states VALUES (NULL, 'Alabama', 'AL');
INSERT INTO states VALUES (NULL, 'American Samoa', 'AS'); and so forth.

Access choked on the create table statement combined, so I created the table
and then tried many variations, but could not get ACCESS to accept multiple
inserts. So my question was, will it do it and I was just coding it wrong?
Claudette

 

[John Spencer]

Access can only run one query at a time. If you want to run more than
one query in sequence then you would need to use some vba to execute the
sql strings.

Dim dbAny as DAO.database
Dim strSQL as string

Set dbAny = currentdb()
strSQL = "INSERT INTO states (Name, Abbrev) VALUES ('Alaska', 'AK')"
DbAny.Execute (strSQL)

repeat.

Now that is doing it the hard way in this case. It would be simpler to
type in the states list and quicker then building a long VBA procedure
to do this.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Claudette Hennessy]

Thank you John, that answers my question. As I said in my initial post, I
eventually copied the info from the PO website into Excel, and then imported
the excel table into Access, which took no time at all.
Claudette