HELP with an Append Query

[GBalcom]

I'm going to try and give a general overview of what I'm trying to accomplish. I have a read only SQL database that product A (estimating software) uses. I have an open SQL database that product B (our ERP system) uses. I need to query certain data from A, and plop it into B.

So far I've accomplished the following:

1. opened both databases through ODBC in an access database.
2. proved out the theory by manually entering a record into product B's quote table to ensure I can create a new record.
3. Added a new table, created records in table to match format of the quote table in product B, then appended records to it through an append query.
4. Created a Query showing the data I want to take out of A and place in B.
5. When I run this as an append query, i receive a primary key error.

I cannot tell or understand what may be causing this error. I have looked into the primary keys, and they all seem to make sense on the input side...i will look into the output side when I finish typing.

Please let me know if there is anything I can post that would help us to solve this.

Thanks,

GB

 

[jdraw]

i receive a primary key error.

What is the full error message and number?
Is it Access, ODBC or SQL database?

When you say SQL database -- what specific database are we talking about?

 

[GBalcom]

Jdraw,
Please see the attached snippet. not sure on which SQL databases....I know product A is a Sybase run application with an SQL Anywhere local server....hope that helps. I'll find out more in the morning.

Thanks!

 

[GBalcom]

I solved it this morning....I was missing 1 required field for the table to create a record.

Now I have a new challenge. Is it possible to automate a series of append queries (there will be 3-4 total). Possibly with a macro? I'd love to have this set up on a switchboard for our users. Not sure where to look to make this happen, as it appears it will need to be a macro.

Thanks,
GB

 

[jdraw]

Glad you got it resolved -- seems to be an Access message "key violation" indicates an attempt to add a record with same key as existing record, or possibly no supplied key (Null)

I say seems because if you attempted to create a duplicate record, the error message usually would say -- didn't update because it attempted to create duplicate ... or something similar.

You could run a series of append (or Update... for an existing record) queries. I don't use macros, I use vba.

I haven't used Sybase or SQL Anywhere.

 

[GBalcom]

Jdraw,
I have 3-4 append queries that I want to run in series.....I've set up a macro just now to accomplish this, but each of them need a specific parameter - the job number.

Is it possible in VBA to call up a routine that would ask for the job number upfront, then pass it to each append query so they can run without asking for the parameter?

I know this would only save seconds each time, but who wants to type in the same number 4 times if they can avoid it!

Thanks....

 

[jdraw]

Yes it's possible. Do you know vba at all?

You might consider a form and a function that takes JOBNumber as a parameter.

Is there some JobNumber generator? How are JobNumbers structured? Is there a predefined list of Jobnumbers that could be used in a drop down(combo) box?

 

[GBalcom]

Jdraw,
I don't have any prior experience with VBA. I know I'll need to learn it at some point. Do you have any recommendations on tutorials?

The Job number is auto-generated from Program A, to push into Program B (it's already part of the data)

Thanks,
GB

 

[jdraw]

I have access 2003 so can not open and use accdb files.
There are tutorials at functionX
http://www.functionx.com/vbaccess2003/index.htm for 2003 and others for 2007 and 2010

but if you have a well defined problem, it might be easy enough to learn by working on the issue with the help of the forum.

What version do you have? If you can create an mdb version, I'll look at ti and work thru it with you.

 

[GBalcom]

Jdraw,
Please see the attached zip file containing a .mdb file. If you take a look at the macro, it shows the 3 append queries that are being used. (quote creation, estimate item, and quote price). the database is all linked tables, so I'm not sure what you'll be able to see, if needed. Let me know if you need anything else.

Thanks for helping,
GB

 

[jdraw]

I looked at your database. I had to remove the reference to Office 14(since I don't have that. I added a reference to Microsoft DAO 3.6 Object Library.

Here is some vba I have created to run your 3 append queries.
I'd like you to look at it and comment/question whatever, until you understand it.
The vba is commented -- strings starting with single quote ' are comments
(I will color them green)

[COLOR="SeaGreen"]'---------------------------------------------------------------------------------------
' Procedure : Run3AppendQueries
' Author    : Jack
' Date      : 12/06/2012
' Purpose   :Sample vba procedure to run the 3 append queries from GBalcom.
'Put query names in an array. Create a For Loop to process the array (each query 'named in the array). As a start, get the  query sql and change the parameter prompt 'with a dummy JobNum for testing. Print the Query SQL and the substituted SQL (with
'JobNum) to show the substitution. Use DEBUG statements to show the SQL.
'
'
'the three append query names are
'QRY_QUOTE_CREATION
'DBA_ESTIMATE_ITEM Query
'QRY_Quote_Price_Creation

'---------------------------------------------------------------------------------------
'[/COLOR]
Sub Run3AppendQueries()

[COLOR="SeaGreen"]'You must identify your variables and their datatype[/COLOR]

Dim tempSQL As String   [COLOR="SeaGreen"]'variable to hold the sql to execute[/COLOR]
Dim JobNum As Long      [COLOR="SeaGreen"]' a variable holding a sample JobNbr[/COLOR]
Dim qdf As DAO.querydef[COLOR="SeaGreen"] 'an Access way to identify a saved query definition[/COLOR]
Dim qNum As Integer     [COLOR="SeaGreen"]'an integer to use to select a queryname from the Querylist array[/COLOR]
Dim QueryList(2) As String  [COLOR="SeaGreen"]'an array to hold the names of the 3 append queries
[/COLOR]
[COLOR="SeaGreen"]'assign the query names to the array positions[/COLOR]
QueryList(0) = "QRY_QUOTE_CREATION"   [COLOR="SeaGreen"]'array  positions start at 0[/COLOR]
QueryList(1) = "DBA_ESTIMATE_ITEM Query"
QueryList(2) = "QRY_Quote_Price_Creation"

 On Error GoTo Run3AppendQueries_Error  [COLOR="SeaGreen"]'some error trapping
 [/COLOR]
[COLOR="SeaGreen"]'Test with a made up JobNum   for TESTING [/COLOR]
JobNum = 34567


For qNum = 0 To 2 [COLOR="SeaGreen"]'qnum is an index into the array QueryList
[/COLOR]
[COLOR="SeaGreen"]'when qnum is 0, we're dealing with the first array position eg QueryList(0)[/COLOR]
[COLOR="SeaGreen"]'and that contains the name of the first query
'
'this next list assigns the sql of QueryList(qnum) to a string variable
' I do this so I don't affect the SQL of the actual query[/COLOR]
  tempSQL = CurrentDb.QueryDefs(QueryList(qNum)).SQL
  
[COLOR="SeaGreen"]'this next line is a debugging aid that prints a value for your info
'In this case it prints the original sql of the query[/COLOR]
   Debug.Print qNum & " " & QueryList(qNum) & " original  " & vbCrLf & tempSQL

[COLOR="SeaGreen"]'next I modify the tempSQL by replacing your [Enter Job Number] with my mock up JobNum[/COLOR]
 tempSQL = Replace(tempSQL, "[ENTER JOB NUMBER]", JobNum)
 
[COLOR="SeaGreen"] 'in the next line I print out the modified SQL to show the JOB NUMBER[/COLOR]
 Debug.Print qNum & " " & QueryList(qNum) & "  modified   " & vbCrLf & tempSQL
 
[COLOR="SeaGreen"] 'This next line should be made active in order for the SQL query to run
 'CurrentDb.Execute tempSQL, dbFailOnError[/COLOR]
Next qNum

   On Error GoTo 0
   Exit Sub

Run3AppendQueries_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Run3AppendQueries of Module Module1"

End Sub

 

[GBalcom]

Jdraw,
All I can say is wow! Thanks a lot for taking the time to help me with this....I've looked at the code twice now and I do have a bunch of questions.

Dim tempSQL As String 'variable to hold the sql to execute
Dim JobNum As Long ' a variable holding a sample JobNbr
Dim qdf As DAO.querydef 'an Access way to identify a saved query definition
Dim qNum As Integer 'an integer to use to select a queryname from the Querylist array
Dim QueryList(2) As String 'an array to hold the names of the 3 append queries

what does the dim stand for above?
why does querylist have (2) at the end of it?
If I were to add another query, would I just have to add it to the array list? (no need to right now, just trying to understand the process)

How do I get this to run? I'm guessing I copy and paste the code into a VBA module?

Is this made so it will prompt the user for the job number?

Thanks alot for all the help, it is much appreciated!

 

[gblack]

I know you didn't mention processing time at all... and maybe you don't need to speed things up. If not, ignore this posts. However, if things are running slowly for you, you might look into passthrough queries as they can often run MUCH faster than ODBC linked tables. Especially since you seem to be linking many tables from your SQL Server Database.

If you haven't done this before, here's how you might go about it.

1. Take each append query you have already created, change it from an append to a select query.
2. Copy the SQL Code of the select to notepad.
3. Convert the code to SQL Server (e.g. single quotes vs. double quotes, date functions etc...).
4. Create a query and close the table window.
5. Choose passthrough query in design tab.
6. Go to properties and set up your ODBC connection by clicking the elipses in the ODBC Connection Str line.
7. Once you've set up the ODBC connection, copy and paste that SQL Server code from notepad into your passthrough query and run it.

Still using the code given the code supplied by JDraw... You can then use each of the passthrough queries within each of your append queries, instead of all those linked ODBC connected tables.

I believe this may speed your process up as the initial processing would be happening on the server. What would be even faster is to use VBA to create the passthrough SQL string on the fly (i.e. with the job number paramter "hard coded" in the SQL string, maybe using an input box or the like)... this would produce a much faster result as all thge processing would happen on the Server Side.

Just a thought...

GL
-Gary

 

[jdraw]

Jdraw,
All I can say is wow! Thanks a lot for taking the time to help me with this....I've looked at the code twice now and I do have a bunch of questions.

Dim tempSQL As String 'variable to hold the sql to execute
Dim JobNum As Long ' a variable holding a sample JobNbr
Dim qdf As DAO.querydef 'an Access way to identify a saved query definition
Dim qNum As Integer 'an integer to use to select a queryname from the Querylist array
Dim QueryList(2) As String 'an array to hold the names of the 3 append queries

what does the dim stand for above?
why does querylist have (2) at the end of it?
If I were to add another query, would I just have to add it to the array list? (no need to right now, just trying to understand the process)

How do I get this to run? I'm guessing I copy and paste the code into a VBA module?

Is this made so it will prompt the user for the job number?

Thanks alot for all the help, it is much appreciated!

I just saw that you had responded as did gblack. GBlack makes good points for passthrough if speed is the issue. However, I saw your immediate issue as relieving the need to enter the same parameter multiple times; a "desire" to learn some vba if it could help the situation and extend your Access skillset.

On that basis, I created a small procedure specific to your issue. I included an Array, a For Loop, and some Debug.Print statements to show some vba constructs and syntax.

Now for your questions:

what does the dim stand for above?
Dim stands for dimension (I think) - it's really the technique in vba to identify a variable to "the system".

why does querylist have (2) at the end of it?
Querylist is an array. It is a one dimentional array (also called a vector by some) that is analogous to a filing cabinet. The (2) represents that this filing cabinet has (believe it or not) 3 storage locations. This quirk is because of something called "0 (zero) addressing". The first location is 0, then 1 then the third is 2. This is pretty rampant throughout programming languages. Somethings are numbered starting at 1, but many start at 0. This is just one of those that starts at 0.

If I were to add another query, would I just have to add it to the array list? (no need to right now, just trying to understand the process)
And that's exactly why I used an array. Yes, you would have to
Dim querylist(3) as string to reserve 4 storage locations. And when you put the new query name into the 4 th location, you would say
QueryList(3) = " the name of the new query goes here"

How do I get this to run? I'm guessing I copy and paste the code into a VBA module?
Yes that is how you would get it to run, but let's handle that a little later.

Is this made so it will prompt the user for the job number?
No, I specifically did not do that at this time,so you would focus on the vba.

Do you see how the For....Next Loop works?

That is a small loop - qnum starts at 0 and processes down to Next, then it goes back to For and increments qnum by 1 (that's the default), processes again and back to For, increments qnum... Once qnum is greater than upper limit of the For ( example: For x = Lower to Upper...) it drops to the next executable line after the Next.


I have to go out for a few hours but will be back later.

Go through the code until you understand it, then we'll get to executing the code different ways, and later will make a form to Prompt the user for a JobNumber, or get the next Job Number from a table or wherever you said it came from.

 

[GBalcom]

gblack,
does the pass thru query require that I have any access to the SQL Server end? (I don't...it's handled by our part time IT guy). I'll definitely keep it in mind for other requirements in the future.

J draw,
The array/loop is really cool. I'm a long way from fully understanding the code you've written, but I think I'd like to outline the main points so I can better understand what you've created.

1. created a variable string to hold the temporary sql code (the code from my append queries) as well as a variable for the Job number.

2. "printed" the existing code, then replaced the value with your job number.

3. made the array so that it looped the #2 process.

4. created an error message.

 

[jdraw]

gblack,
does the pass thru query require that I have any access to the SQL Server end? (I don't...it's handled by our part time IT guy). I'll definitely keep it in mind for other requirements in the future.

J draw,
The array/loop is really cool. I'm a long way from fully understanding the code you've written, but I think I'd like to outline the main points so I can better understand what you've created.

1. created a variable string to hold the temporary sql code (the code from my append queries) as well as a variable for the Job number.

2. "printed" the existing code, then replaced the value with your job number.

3. made the array so that it looped the #2 process.

4. created an error message.

#1 Yes, I created a temporary variable to hold the original query sql. That sql is stored within Access because you saved the query(ies) and gave them names. So by using
tempSQL = CurrentDb.QueryDefs(QueryList(qNum)).SQL
I assign to tempSQL, the SQL representing your query.
Breaking it down:
CurrentDb.---This current database
QueryDefs --- in the stored Query definitions
(QueryList(qNum)) ----the query name in the Querylist array in box qnum
.SQL --- the SQL for that query

Then I modified the SQL by replacing your "[ENTER JOB NUMBER]" string
with the value of the phoney job number (JobNum) and assigned that to tempSQL.
tempSQL = Replace(tempSQL, "[ENTER JOB NUMBER]", JobNum)

This way your original SQL is never altered. Only the tempSQL ("copy" of your query's sql gets changed and executed )we'll do that later.

#2 The DEBUG.PRINT statements are a means of debugging. It let's you see the value of some fields, in this case the Query SQL, and the query sql with the JobNum substitution.

#3 By using the array within the FOR LOOP, I only need to define the processing steps once. Since the JobNum is the same for all queries, all we have to do is "Loop" through the query names and repeat the process.

#4 The Error message is part of an Error Handling routine that will present the Error number and description if an error occurs.

Good questions.

 

[jdraw]

GBalcom, I don't know if you solved your issue, or if you have dropped the topic, but you may want to review

http://www.functionx.com/vbaccess2003/Lesson05.htm

regarding modules, procedures etc.

Good luck with your project.

 

[GBalcom]

Jdraw,
It's neither solved nor dropped.....I've just been chin deep in work lately....probably for another week. I simply don't have time right now at work to devote to learning VBA.
However at home, I just recieved a book I bought on Access 2010 (the missing manual). It seems to have 60-70 pages of good info on VBA, so I'll start there when I can.

Thanks for your help so far!

GB

 

[GBalcom]

Ok I'm back......I'd like to see if I can implement this (at least a rough draft) today to see if it's what I need. I'm going to try and run this myself, but if I can't I'll ask for more help from the group.

Thanks,
GB

 

[GBalcom]

Update

well, I'm 7 months into my VBA journey and it seems I've come full circle...back to this problem that is...

I've modified Jdraw's code as below:

Public Sub Run3AppendQueries(intExpectedLines As Integer)
On Error GoTo Error_Handler

'code
'---------------------------------------------------------------------------------------
' Procedure : Run3AppendQueries
' Author    : Jack
' Date      : 12/06/2012
' Purpose   :Sample vba procedure to run the 3 append queries from GBalcom.
'Put query names in an array. Create a For Loop to process the array (each query 'named in the array). As a start, get the  query sql and change the parameter prompt 'with a dummy JobNum for testing. Print the Query SQL and the substituted SQL (with
'JobNum) to show the substitution. Use DEBUG statements to show the SQL.
'
'
'the three append query names are
'QRY_QUOTE_CREATION
'DBA_ESTIMATE_ITEM Query
'QRY_Quote_Price_Creation

'---------------------------------------------------------------------------------------
'


'You must identify your variables and their datatype

Dim tempSQL As String   'variable to hold the sql to execute
Dim JobNum As Long      ' a variable holding a sample JobNbr
Dim SumNode As Long     ' a variable to hold the summary node
Dim qdf As DAO.QueryDef 'an Access way to identify a saved query definition
Dim qNum As Integer     'an integer to use to select a queryname from the Querylist array
Dim QueryList(2) As String  'an array to hold the names of the 3 append queries
Dim intRecordsQuote As Integer  'holds records affected for a message box after updates
Dim intRecordsLine As Integer
Dim intRecordsPrice As Integer


'assign the query names to the array positions
QueryList(0) = "qryQuoteCreation"   'array  positions start at 0
QueryList(1) = "qryEstimateItem"
QueryList(2) = "qryEstimateItemPrice"

 On Error GoTo Error_Handler  'some error trapping
 
'Test with a made up JobNum   for TESTING
JobNum = Me.QuoteNum
SumNode = Me.SumLevel



For qNum = 0 To 2 'qnum is an index into the array QueryList

'when qnum is 0, we're dealing with the first array position eg QueryList(0)
'and that contains the name of the first query
'
'this next list assigns the sql of QueryList(qnum) to a string variable
' I do this so I don't affect the SQL of the actual query
  tempSQL = CurrentDb.QueryDefs(QueryList(qNum)).SQL
  
'this next line is a debugging aid that prints a value for your info
'In this case it prints the original sql of the query
   Debug.Print qNum & " " & QueryList(qNum) & " original  " & vbCrLf & tempSQL

'next I modify the tempSQL by replacing your [Enter Job Number] with my mock up JobNum
 'tempSQL = Replace(tempSQL, "[ENTER JOB NUMBER]", JobNum)
 
 'in the next line I print out the modified SQL to show the JOB NUMBER
 'Debug.Print qNum & " " & QueryList(qNum) & "  modified   " & vbCrLf & tempSQL
 
 'This next line should be made active in order for the SQL query to run
 CurrentDb.Execute tempSQL, dbFailOnError
 
 'record number of records affected
 Select Case qNum
    Case 0
        intRecordsQuote = CurrentDb.RecordsAffected
    Case 1
        intRecordsLine = CurrentDb.RecordsAffected
    Case 2
        intRecordsPrice = CurrentDb.RecordsAffected
    Case Else
        GoTo Error_Handler
  End Select
  
Next qNum

'set up message boxes
    If intExpectedLines = intRecordsLine = intRecordsPrice And intRecordsQuote = 1 Then
        MsgBox "Conversion was Successful", vbInformation, "Conversion Complete"
        
    ElseIf IsNull(intRecordsQuote) Or intRecordsQuote = 0 Then
        MsgBox "Quote Number did not convert" & vbCrLf _
        & "Please ensure the quote does not exist already in Visual, then try again", vbCritical, "Conversion Unsuccessful"
        
    ElseIf intRecordsLine <> intExpectedLines Then
        MsgBox intRecordsLine & " of " & intExpectedLines & " converted." & vbCrLf _
        & "Please adjust as necessary in Visual", vbCritical, "Inconsistent Results"
        
    
    ElseIf intRecordsLine <> inRecordsPrice Then
        MsgBox "Not all Lines came over with a price associated" & vbCrLf _
        & "Please ensure the pricing in visual is as expected" & vbCrLf _
        & intRecordsLine & " Lines were converted to Visual" & vbCrLf _
        & intRecordsPrice & " prices were populated to Visual ", vbInformation, "Incomplete Line Pricing"
    
    End If
    
        
    
        
        
    
    
    
    








Exit_Procedure:
    On Error Resume Next
    Exit Sub
    
    
Error_Handler:
    DisplayErr Err.Number, Err.Description, "Form_Import_Var", "Run3AppendQueries"
    Resume Exit_Procedure
    Resume
End Sub

However, I can't seem to get the SQL to work correctly. Frankly I'm unclear in what to replace, because in the immediate window, the values of the form control values seem to pop up correctly when I float over them. Please see the first queries SQL below:

INSERT INTO dbo_QUOTE ( ID, CUSTOMER_ID, NAME, ADDR_1, ADDR_2, ADDR_3, CITY, STATE, ZIPCODE, CONTACT_FIRST_NAME, CONTACT_LAST_NAME, SALESREP_ID, TERRITORY, STATUS, QUOTE_DATE, WIN_PROBABILITY, SHIP_VIA, FREE_ON_BOARD, TERMS_NET_TYPE, TERMS_DISC_TYPE, FREIGHT_TERMS, CREATE_DATE, CURRENCY_ID, ENTITY_ID, USER_ID, STATUS_EFF_DATE )
SELECT "Q-" & [JOB_TAG] AS [Quote Number], DBA_CUSTOM_DATA_COMPANY.TAG5 AS CUSTOMER_ID, DBA_COMPANY.NAME, DBA_COMPANY.STREET_1 AS ADDR_1, DBA_COMPANY.STREET_2 AS ADDR_2, "" AS ADDR_3, DBA_COMPANY.CITY, DBA_COMPANY.STATE, DBA_COMPANY.ZIP_5 AS ZIPCODE, "" AS CONTACT_FIRST_NAME, "" AS CONTACT_LAST_NAME, DBA_CUSTOM_DATA_SALESREP.TAG5 AS SALESREP_ID, DBA_CUSTOM_DATA_SALESREP.TAG6 AS TERRITORY, "A" AS STATUS, Date() AS QUOTE_DATE, "0.25" AS WIN_PROBABILITY, "BEST WAY" AS SHIP_VIA, DBA_JOB.DESCRIPTION AS FREE_ON_BOARD, "A" AS TERMS_NET_TYPE, "A" AS TERMS_DISC_TYPE, "B" AS FREIGHT_TERMS, Date() AS CREATE_DATE, "US" AS CURRENCY_ID, "1" AS ENTITY_ID, DBA_CONTACT_ESTIMATOR.FIRST_NAME AS USER_ID, Date() AS STATUS_EFF_DATE
FROM ((DBA_CUSTOM_DATA AS DBA_CUSTOM_DATA_COMPANY INNER JOIN (DBA_COMPANY INNER JOIN (DBA_CONTACT AS DBA_CONTACT_ESTIMATOR INNER JOIN DBA_JOB ON DBA_CONTACT_ESTIMATOR.CONTACT_NBR = DBA_JOB.EstimatorNbr) ON DBA_COMPANY.COMPANY_NBR = DBA_JOB.COMPANY_NBR) ON DBA_CUSTOM_DATA_COMPANY.ID = DBA_COMPANY.COMPANY_NBR) INNER JOIN DBA_ROLE ON DBA_JOB.JOB_NBR = DBA_ROLE.JOB_NBR) INNER JOIN (DBA_CUSTOM_DATA AS DBA_CUSTOM_DATA_SALESREP INNER JOIN DBA_CONTACT AS DBA_CONTACT_SALESPERSON ON DBA_CUSTOM_DATA_SALESREP.ID = DBA_CONTACT_SALESPERSON.CONTACT_NBR) ON DBA_ROLE.CONTACT_NBR = DBA_CONTACT_SALESPERSON.CONTACT_NBR
WHERE ((("Q-" & [JOB_TAG])="Q-" & [Forms]![Import_Var]![QuoteNum]) AND ((DBA_ROLE.ROLE_REF_ID)=203));

Any insight would be greatly appreciated, as solving this is my goal for the day