Record by record query (1 Viewer)

The Archn00b

Registered User.
Local time
Today, 16:07
Joined
Jun 26, 2013
Messages
76
Hello everyone,

I have an append query that I would like to use to distribute records to a variety of databases. Some records will go to one, others to a second and so on.

I have an idea as to how this would work in Visual Basic, but how can I make an append query work through a table on a record by record basis?

I'm assuming that this whole task is best done in VB, but I might be able to do it in SQL?

Thanks!
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:07
Joined
Aug 11, 2003
Messages
11,696
You can do it in SQL if you can pre-identify the target database by the records (somehow) by using a where clause.
4 Different databases, 4 different Append queries with 4 different where clauses....
 

The Archn00b

Registered User.
Local time
Today, 16:07
Joined
Jun 26, 2013
Messages
76
OK, so I figured it out using a DAO. A Recordset in this case and a loop.

Code:
Do Until Recordset.EOF
    Dim [Recordset_Value1]...
    'Create Destination Database Pathway for each record
    Dim Pathway
    If [Recordset_Value1] = "1" Then
    Pathway = "[Pathway_1]"
    ElseIf [Recordset_Value1] = "2" Then
    End If
    'Creating SQL Append Command to distribute records to correct destination database
    Dim AppendCommand
    AppendCommand = "INSERT INTO Table1 (Field1, Field2) IN" & Pathway & ";"
    'Run Append Command
    CurrentDb.Execute AppendCommand
    'Move to next item of the Recordset (if there is one)
    Recordset.MoveNext
Loop

However when I run this, I get an error "Object doesn't support this property or command."

The debugger points to:

Code:
Do Until Recordset.EOF

I've tired adding "= True" to the end of the part directly above, but the error remains.

Any ideas? Thanks!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:07
Joined
Jan 20, 2009
Messages
12,849
Firstly Recordset is a reserved word and not suitable as an object name.

Once you fix this you will find that the SQL command is not a valid syntax.

Why not just link the tables you want to write to?

BTW Sending records to different databases sound like an odd requirement. Why are you doing this?
 

The Archn00b

Registered User.
Local time
Today, 16:07
Joined
Jun 26, 2013
Messages
76
Firstly Recordset is a reserved word and not suitable as an object name.
I have it as a variable name:

Code:
Dim Recordset
Recordset = CurrentDb.OpenRecordset(Query)

But I'll call it something else.

Once you fix this you will find that the SQL command is not a valid syntax.

I've looked at it again and I've got:

Code:
Dim AppendCommand
AppendCommand = "INSERT INTO Observations (Species_ID, Date_Of_Sighting, Longitude, Latitude, Number_Sighted, Comments, Recorder_ID, Verifier_ID) IN '" & Pathway & "';"

It's missing the SELECT statement that usually goes with an append query, but seeing that I've already used a SELECT statement to define the Recordset (Query), I've just left it out. I didn't think it was needed.

BTW Sending records to different databases sound like an odd requirement. Why are you doing this?

Ah. It's part of a data verification process and it also leaves room for the database to grow. At least is my understanding.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:07
Joined
Jan 20, 2009
Messages
12,849
I have it as a variable name

Yes, an object variable.
Object variables must be Set.
You should also be declaring the types for your variables. Otherwise they all become Variants.

Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(Query)

Code:
Dim AppendCommand
AppendCommand = "INSERT INTO Observations (Species_ID, Date_Of_Sighting, Longitude, Latitude, Number_Sighted, Comments, Recorder_ID, Verifier_ID) IN '" & Pathway & "';"

It's missing the SELECT statement that usually goes with an append query, but seeing that I've already used a SELECT statement to define the Recordset (Query), I've just left it out. I didn't think it was needed.

You have defined where the values are to be entered but not given the query any values. Values don't magically get from the recordset to the query string without being referred to.

Ah. It's part of a data verification process and it also leaves room for the database to grow. At least is my understanding.

Verification?

If you have so much data to be splitting it up into separate database you probably should be using a different database system.

How much data are you expecting?
 

The Archn00b

Registered User.
Local time
Today, 16:07
Joined
Jun 26, 2013
Messages
76
Yes, an object variable.
Object variables must be Set.
You should also be declaring the types for your variables. Otherwise they all become Variants.

Got it. Thanks. That's solved the error. but now I get a new one.

Syntax error in INSERT INTO statement

But it's got nothing to do with what you pointed out in:

You have defined where the values are to be entered but not given the query any values. Values don't magically get from the recordset to the query string without being referred to.

As I already had those declared before my first code example in the first post. I just didn't include them because it wasn't part of the problem.

At the moment the INSERT INTO statement/append query is:

Code:
AppendCommand = "INSERT INTO Table (Field1, Field2) IN '" & Pathway & "';"
CurrentDb.Execute AppendCommand

Does CurrentDb.Execute AppendCommand Execute the Append Command on the Recordset or the entire database? I need it just on the Recordset.

Pathway was declared in:

Code:
 Dim Pathway As String
    If [Recordset_Value1] = "1" Then
    Pathway = "[Pathway_1]"
    ElseIf [Recordset_Value1] = "2" Then
    Pathway = "[Pathway_1]"
    End If

I'm wondering if it's got anything to do with the spacing. Going to try to fix it.
 
Last edited:

The Archn00b

Registered User.
Local time
Today, 16:07
Joined
Jun 26, 2013
Messages
76
Fixed the problem. I went for a different kind of append query, one that used VALUES rather than FROM (which is what I was basing it off) . I was able to use it in the same way as I wanted by just declaring some more variables out of the Recordset.

Thanks for the help! ;)
 

Users who are viewing this thread

Top Bottom