INSERT INTO and SELECT statments

[HOCP4ME]

I'm building a database with Office 97 (yes, I know it's outdated). To do most of the things I need to do, I will have to use the "INSERT INTO" and "SELECT" statments. The problem is, I can't seem to get either of these to work in code.

I try using the INSERT INTO statments like this: INSERT INTO destination SELECT source.field FROM source WHERE field = x

For example:
INSERT INTO([Cancelled Customers]) SELECT [Current Customers].[Customer ID] FROM [Current Customers] WHERE [Customer ID] = x
That is all one line.
If I put it all on one line like the reference file says, I get an error because the compiler expected "end of statment" right before "SELECT".
I also tried it on seperate lines, like this:
INSERT INTO([Cancelled Customers])
SELECT [Current Customers].[Customer ID]
FROM [Current Customers]
WHERE [Customer ID] = x
If I do that, three of the lines work fine, except the SELECT line does something odd. I get a compliler error that says "expected: case" right after "SELECT", as if I was using the "Select Case" statment rather than the SELECT statment.

This doesn't just happen when using the INSERT INTO statment. Anytime I try to use the SELECT statment, even on it's own, the compliler thinks I'm actually meaning to use the Select Case statment.

Basically, I would like to know the proper way to do a INSERT INTO and a SELECT statment in Microsoft Access 97 (using VBA). Although upgrading MS Office would probably provide me with an easier way to accomplish what I need to, I know it is possible in Access 97, because it is in the help file, I guess I just can't get the syntax right.

Thanks for any help. I'm somewhat new to programming, and while I have a general understanding of how it works, there are some statments like this one that I just can't get to work. Any help would be greatly appreciated.

Thanks!

 

[Inalende]

Not sure if this will work for Office 97 because it's too old for me to know, but the proper syntax for what you're trying to do is as follows:

SELECT column_name(s) INTO newtable [IN externaldatabase]

So something like

SELECT [Current Customers].[Customer ID]
INTO([Cancelled Customers])
FROM [Current Customers]
WHERE [Customer ID] = x

Or whatever your code is, check the following link for the proper syntax and other options for it.
From:

W3Schools

 

[HOCP4ME]

Not sure if this will work for Office 97 because it's too old for me to know, but the proper syntax for what you're trying to do is as follows:

SELECT column_name(s) INTO newtable [IN externaldatabase]

So something like

SELECT [Current Customers].[Customer ID]
INTO([Cancelled Customers])
FROM [Current Customers]
WHERE [Customer ID] = x

Or whatever your code is, check the following link for the proper syntax and other options for it.
From:

W3Schools

According to the help file, in Access 97, SELECT INTO is used when the destination is a new table, whereas INSERT INTO SELECT is used when the destination is an existing table. Since I want the destination to be an existing table, I think I need to use INSERT INTO SELECT.

Neither of these (INSER INTO or SELECT INTO), however, will work. That brings me to my real problem:

Whenever I try to do a SELECT statment, the VB compiler thinks I'm using a Select Case statment, and therefore expects "case" to come right after the "SELECT".


Sorry to bother everyone, but it would be great if I could figure out how to use a SELECT statment. I can't do much with my database without use of this statment.

Thanks for the help Inalende and anyone else who replies.

 

[Y2K SE]

You are correct that you need an INSERT INTO

SELECT [data] command, not a SELECT [data] INTO

command.

It should not make a difference if you enter the statement as one or multiple lines. My guess on the multiple line attempt is that you aren't probably declaring a multiline statement in VBA, ala:

strSQL = "SELECT [Current Customers].[Customer ID] " & _
    "INTO ([Cancelled Customers]) " & _
    "FROM [Current Customers] " & _
    "WHERE [Customer ID] = x"

If you do this properly, the compiler will detect that the SELECT is part of a string, not a reserved word.

What I usually do when I'm having a problem with a query is to build the query within the Access query designer, then flip to the SQL view and copy/paste the code.

 

[HOCP4ME]

Okay, I still havn't gotten it to work yet, but I think I'm understanding it more.

I have to tell VBA that what I'm entering is an SQL statment using "strSQL =", correct? That would explain I couldn't get it to recognize the "SELECT" statment before, because I didn't use "strSQL" because I was using an SQL statment.

Now, however, the compiler says "variable undefined" right after "strSQL =", as if "strSQL" were a variable.

I have a feeling now I'm just doing something really stupid that I can't see, so maybe you guys can see it for me. What I want to happen is, when I click the button "command174" on a form, the current record will be located in "Current Customers", and the Customer ID will be placed into "Cancelled Customers". Here is exactly what's in my code window for the form I want to do this from, with the variable "x" removed for simplicity (I can do that later):

Option Compare Database
Option Explicit

Private Sub Command174_Click()
strSQL = "INSERT INTO([Cancelled Customers])" & _
    "SELECT [Current Customers].[Customer ID]" & _
    "FROM [Current Customers]" & _
    "WHERE [Customer ID] = 2"
End Sub

This is when I get the "variable undefined" error. Do I need to end the SQL statment at the last line? Is there something else I need to do to start and SQL statment? Or, am I doing this completely wrong?

I really hate to be one of those people who posts a "here's what I want to do now do it for me" demand. If I can get this one situation to work, however, I will be able to use the same statments elsewhere in the database numerous times. I won't be able to do much with this database if I can't use SQL statments in VBA code.

All help so far is greatly appreciated, and please forgive me for asking questions that probably have obvious answers. While this is not my first time using VB, it is my first time using it within MS Access.

 

[HOCP4ME]

Whoa! After reading the help file some more, I realized I was wwwaaayyy off.

strSQL is a variable, and need to be defined as a string.
This variable can then be used as a QueryDef to append data to a table.

I will now try something with this. You can totally ignore my previous post.

 

[DocNsane]

Ah...VB(5-6) and Access...the pain in the ass that keeps on giving...

 

[HOCP4ME]

I have two items of good news:

1. I have basically got it working now, just a few more methods I have to learn but nowhere near as complicated as this one.

2. I will be getting Office 2003 this week!!! I agree that VB.Net is soooo much easier to work with. Is it included in Office 2003 Professional? (within MS Access I mean, not as a seperate compiler)?

 

[DocNsane]

Technicaly, the .NET compiler and libraries are all free downloads. As is the express versions of the IDE, and I even think MS SQL Server Express.

So yeah, you'll have all you need probbaly.

 

[Y2K SE]

2. I will be getting Office 2003 this week!!! I agree that VB.Net is soooo much easier to work with. Is it included in Office 2003 Professional? (within MS Access I mean, not as a seperate compiler)?

Office 2003 continues to use a streamlined version of VB 6 syntax, formally called Visual Basic for Applications. There's many new features that makes life easier than Access 97, but the syntax is basically the same. You can make Office Add-Ins using the .NET IDE.