Alternative to Append Query (1 Viewer)

ECEK · May 24, 2016

I used to create a !holding table! in which I would ......hold my data before appending it to the main database.

My command button would use an append query (then when I wanted to use the holding table again I would use a delete query to "cleanse" the holding data and so on.

My question is:

I have created a form without the holding data and want to learn the procedure for "appending" the virtual data to my database.

In essence I need to get
Form1.Field1
Form1.Field2 etcetc into my database via a command button.

Hope this makes sense ?

Thanks in advance

Isskint · May 24, 2016

I think you have explained that very well.

Using a separate ImportTable is pretty much the standard practice IMHO.

Where would the 'virtual' data come from?
If it is on the clipboard you could use paste manually (or use DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70 from a button click). If the data is in another table, spreadsheet, textfile etc you may be able to use some VBA to copy the data to the clipboard and then use the options above OR read/write record by record using VBA.

Why do you want to get away from using an import table?

Minty · May 24, 2016

Assuming your target table is called Target write a direct SQL Append query. Create the query as a string then execute it (air code);

Code:

Dim sSql as String

sSql = "INSERT INTO Target (TargetField1Number, TargetField2String, etc) VALUES (" & Me.FormNumberField1  & " , '" & Me.FormStringFIeld2 & "' ) ;"

Debug.Print sSql   [COLOR="Green"]'Remove this once you have the format correct[/COLOR]

' Currentdb.Execute sSql    [COLOR="green"]'Put this in once you are happy with things[/COLOR]

arnelgp · May 24, 2016

use insert query on your button's click event:

private sub button_click()
docmd.runsql "insert into yourHoldingTableName (field1, field2, ..., fieldN)
select " & nzNew(Me.Field1) & "," & NzNew(Me.Field2) & "," & ... NzNew(FieldN)
end sub

private function nzNew(v As Variant) As Variant
If IsNull(v) then
nzNew = "Null"
Exit function
End If
If IsNumeric(v) then
nzNew = v
Exit function
end if
if IsDate(v) then
nzNew = "#" & Format(v, "mm/dd/yyyy") & "#"
Exit function
end if
nzNew = Chr(34) & v & Chr(34)
End function

Simon_MT · May 24, 2016

You can use one file providing you have a flag NewData. I need to add additional information to ETL data and clear this down once the process has finished. There are three updates from this data - 300,000 p.a. records are used to create KPI information that can be summarised to about 30,000 records. This is for efficiency and ease of use - your method is sound.

Simon

Simon

ECEK · May 24, 2016

This is the code that I'm using from Minty. However In getting a Runtime error 3075 syntax error (missing operator) in query expression ?

Note that the table that this data needs to go to is a linked table. Also: The unbound fields on the form are the same names as in the table.

Can anybody see any glaring errors in this:

Code:

Private Sub cmd_addnewcase_Click()
Dim sSql As String

sSql = "INSERT INTO tbl_cases_pending (np_branch, rep_status, so_ref,consultant,para,dpiadmin,client,clientref,inbf,rec_type,date_fr)VALUES (" & Me.np_branch & " , '" & Me.rep_status & "',& Me.so_ref & " ' ,& Me.consultant & "' ,& Me.para & "' ,& Me.dpiadmin & "' ,& Me.client & "' ,& Me.clientref & "' ,& Me.inbf & "' ,& Me.rec_type & "' ,& Me.date_fr & "'  ) ;"

CurrentDb.Execute sSql

End Sub

Minty · May 24, 2016

You are missing a space between the ) and VALUES .

Your quotes are all a bit out, the format you are trying to create is VALUES ('String','String2',etc)
You currently are creating VALUES ('String1','" String2 <<< This is wrong. Each String should have '" & Me.rep_status & "' , '" & Me.so_ref & "' , '" etc

If you look at the immediate window and put back in the Debug.Print sSql you will see the string it is trying to evaluate.

ECEK · May 24, 2016

Sorry about this !!

Code:

Private Sub cmd_addnewcase_Click()
Dim sSql As String

sSql = "INSERT INTO tbl_cases_pending (np_branch, rep_status, so_ref,consultant,para,dpiadmin,client,clientref,inbf,date_fr) VALUES ( '" & Me.np_branch & "','" & Me.rep_status & "','" & Me.so_ref & "','" & Me.consultant & "','" & Me.para & "','" & Me.dpiadmin & "','" & Me.client & "','" & Me.clientref & "','" & Me.inbf & "','" & Me.date_fr & "') ;"
'Debug.Print sSql
CurrentDb.Execute sSql

End Sub

syntax error then the CurrentDb.Execute sSql is yellowed out.

I'm ever so sorry, my VBA is rather limited.

Minty · May 24, 2016

Make sure that all your data types are correct. The below stolen from http://www.fontstuff.com/access/acctut15.htm
Data Type Qualifiers

When supplying values to an SQL statement, for example as query criteria, their data type must be correctly defined by a "qualifier". This is done by enclosing the value between a pair of appropriate characters.

Text must be enclosed in either single quotes (') or double quotes ("), for example:

WHERE tblStaff.Department = "Marketing”

or

WHERE tblStaff.Department = 'Marketing’

A Date should be enclosed in hash marks (#) also called pound or number signs, for example:

WHERE tblStaff.BirthDate = #09/27/1950#

A number, of any sort, needs no qualifier and can be entered as it is, for example:

WHERE tblInvoices.InvoiceNumber > 1500

Get the Date Format Right

Dates in SQL must be written in the US date format (month/day/year). This is imperative regardless of the default date format settings on your computer. The Access query design window accepts dates in your local default format but it converts the date you type to the correct format when it builds the SQL statement.

arnelgp · May 24, 2016

sSql = "INSERT INTO tbl_cases_pending (np_branch, rep_status, so_ref,consultant,para,dpiadmin,client,clientref,inbf,date_fr) SELECT newNz(Me.np_branch) & "," & newNz(Me.rep_status) & "," & newNz*Me.so_ref) & "," & newNz(Me.consultant) & "," & newNz(Me.para) & "," & newNz(Me.dpiadmin) & "," & newNz(Me.client & "," & newNz(Me.clientref) & "," & newNz(Me.inbf) & "," & newNz(Me.date_fr) & ";"

then use my function so it will take care if it is string, null, numeric or date.

Alternative to Append Query (1 Viewer)

ECEK

Registered User.

Isskint

Slowly Developing

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Simon_MT

Registered User.

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread