Append Queries in a Access Database Project

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Originally my database was an mdb file and the database user would click an option button on a form which would run an append query that automatically appended a list of areas from a table named MasterAreaList to a table named PersonsAreas and for each area the append query would also store the PersonsID and it would get this from the form

Now I have upsized to an Access adp file my append queries will not go across and I cannot get the append query to look at the form and automatically store the PersonID against each area.

This was sooo easy in Access and appears to be impossible in adp? I have read many articles and seen others pose this question on others site and not get a successful answer. I can make an append query but can't find information on getting the append query to pull an ID from a form. Any help would be much appreciated
 
Dear Cathi:

We made the switch from MDBs to ADPs for all our applications over 3
years ago with the advent of Access 2000. So perhaps I can be of some
help.

You have correctly defined the problem. And that's a bit part of the
job. So let's try to finish this up.

The problem is that the ADP cannot reference your Access controls.

There are two fairly simple, direct ways of overcoming this that come
to mind:

- In your application, write the SQL to perform the function in code.
That is, build the SQL string and submit that.

- In your application, call a stored procedure that performs the
function, with the values of your controls passed as parameters to the
function.

If you have any skill at coding and debugging VBA, plus the ability to
write the T-SQL for either the append query (first option above) or
the stored procedure, then this should not be much of a challenge.
But then, if you knew all this, you probably wouldn't be asking.

Likely, you'll want me to help write this. I usually go with the
first option. My applications already have around a hundred stored
procedures without creating one for every little append, update, or
delete that comes along. If I used the stored procedure route, I'd
have many hundreds of stored procedures, and that would make the
management of my projects more difficult.

Please post the SQL of your existing MDB query and I try to work with
you from there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Dear Tom
Wow, you would do that for me, I must say I am impressed and grateful.
I have many of these queries but if you could help me with the one I posted, I'm sure I can take it from there.
The SQL of the existing MDB query is:

INSERT INTO tblDecoratingFloorings ( ContactID, Area )
SELECT Forms!frmInterface!ContactID AS Expr1, tblAreasMaster.Area
FROM tblAreasMaster;

Again thank you :)
 
Dear Cathi:

Watch out for things that wrap in the code. Hard for me to control in
a posting.

Untested code below!

Public Procedure InsertDecoratingFloorings()
Dim strSQL As String

strSQL = "INSERT INTO tblDecoratingFloorings(ContactID, Area) " &
_
"SELECT " & CStr(Me.ContactID) & ", Area FROM tblAreasMaster;"
DoCmd.RunSQL strSQL
End Procedure

I've chopped of the alias "AS Expr1" and the fully qualified reference
"tblAreasMaster.Area" I shortened to just "Area" since there is only
one table in the select.

If there is any problem, put a break point on the DoCmd.RunSQL and
display strSQL in the immediate pane. See if it reads right. Paste
the query into Query Analyzer if you need to test it.

If it weren't for the debugging you get by creating the string before
you run it, you could do the whole thing with just this:

DoCmd.RunSQL "INSERT INTO tblDecoratingFloorings(ContactID, Area)"
& _
"SELECT " & CStr(Me.ContactID) & ", Area FROM tblAreasMaster;"

If you have a value that is a string literal, you will need to add
single quotes around it. Same with dates. If the literal may contain
a single quote (apostrophe) you need to double it.

Oh, do you have Query Analyzer? You can get this and Enterprise
Manager by purchasing SQL Server Developer Edition. It's only about
$50 and indispensible for what you're going to be doing. Stronly
recommended!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Just wanted to say ... thank you.

Your information has opened a whole new SQL/VBA learning curve. Only one thing tripped me up for quite sometime, I was extending the statement you gave me with WHERE clauses and I couldn't for the life of me get one of them to go, so resorted to the Query Analyzer that you encouraged me to look at, and bang!

Couldn't believe it for so long Access I have had to remember that in Access No is 0 and Yes is -1, which never felt right! And now in SQL No is 0 (zero) and Yes is 1, but of course I was checking everything else but this for ages, never mind.

Thanks again Cathi
 
Dear Cathi:

So many we try to help in this newsgroup just want someone to do their
work for them, or just overcome a small issue.

Your response makes me feel I have contributed in a small way to a
much more beneficial cause. But it hasn't really made you life
easier, but opened a whole new avenue of inquiry and education.

Fun, isn't it! Keep that attitude - it will serve you well.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom
I vowed and declared I was not going to bother you as you guys must be extremely busy, I can't believe the number of postings that appear daily, your help has been immense and you would be extremely proud of me, I have been going great guns all day. But could I impose one more time pleeeease *big smile*

I have been fiddling and fiddling and I know I missing something small, all I want to do is concatentate the text "Exterior Cladding 2" with the field tblExteriorCladding.CladdingType 2 to form my field Product, how would you suggest I do this.

My SQL statement is this:

strSQL = "INSERT INTO tblDecoratingExterior(ContactID, Product, Link, BrochureID) " & _
"SELECT " & CStr(Forms!frmInterface!ContactID) & ", 'Exterior Cladding 2' & tblExteriorCladding.CladdingType2 , tblExteriorCladding.CladdingType2, tblListExterior.Brochure " & _
"FROM tblExteriorCladding LEFT JOIN tblListExterior ON tblExteriorCladding.CladdingType2 = tblListExterior.Product;"
DoCmd.RunSQL strSQL

Again I am very thankful for your time and support. Cathi :)
 
Dear Cathi:

Putting a breakpoint on the DoCmd line, you can then open the
immediate pane and enter:

? strSQL

It will display the SQL of your query. Paste that into Query Analyzer
and see if it runs and works as desired (using a "test" database, not
the live production database). Often this will show you better just
where the problem is. You can fix the query there, then adjust the
code to produce the changed SQL.

The code you show should produce something like this:

INSERT INTO tblDecoratingExterior(ContactID, Product, Link,
BrochureID) SELECT 12345, 'Exterior Cladding 2' &
tblExteriorCladding.CladdingType2 , tblExteriorCladding.CladdingType2,
tblListExterior.Brochure FROM tblExteriorCladding LEFT JOIN
tblListExterior ON tblExteriorCladding.CladdingType2 =
tblListExterior.Product;

It appears you have avoided the pitfall of failing to place spaces at
the end of lines where you have concatenated the string fragments.

The concatenation operator for SQL Server is not & but +. This is the
only readily apparent error I see.

When building such a query, I would first write and test just the
SELECT portion in QA (Query Analyzer), then add the INSERT and test
again. I would paste the finished SQL into the code, then make the
changes to generate the string with the concatenated "parameters" as
shown. If you do this, you may have fewer problems debugging.
Putting the whole thing together and then discovering there is a
problem "somewhere" is doing it the hard way (although after you've
had enough practice, you may want to skip some steps).

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Magic! It's the little things between Access and SQL that are getting me, the + instead of & solved it. I am disappointed I couldn't see that it's like the boolean 0/1 issue earlier. Thanks so much, have an awesome day! Cathi :)
 
Back
Top