Is it possible to ommit fields in SQL?

[Guest]

Sorry F/folks - I'm an SQL novice

I'm trying to write a small bit of SQL to append records to a table in
Access2000. I have data held in a staging table and I want to append too all
fields but one (the primary key - an autonumber field) to the main table

I know it could be done by including all the other fields but I want this
piece of code to be usable for all my tables and all my tables use the same
field name for their primary key.

This is what I have so far but it fails I think due to trying to copy a
number onto an autonumber key field (I get a message saying Changes not
successful due to duplicate key values)


strSQL = "INSERT INTO tbl" & strCurrentTable & " SELECT staging_tbl" &
strCurrentTable & ".* FROM staging_tbl" & strCurrentTable & " WHERE lngKey="
& lngCurrentKey
CurrentDb.Execute strSQL, dbFailOnError


If my primary key is called lngTableKey how would I change this code to
append everything except lngTablekey

Thanks in advance

Simon

 

[Ken Snell [MVP]]

You would do what you want by specifying all the other fields in a VALUES()
clause. Sorry, but there is no shortcut for omitting a field.

If you want to use this as a "generic" method, then your code will need to
identify which field is an autonumber field, and then build a string that
contains all field names except that one, and then concatenate that string
into your SQL statement that you're building.

 

[BJ Freeman]

short answer yes.

before we get to far.
you are using an SQL server. it has what is know as Stored proceedures.
you can write a stored procedure that
take the input vars and inserts them into the the table.
You can open the SQL servers Books online for examples.
the stored proceedure has the insert with (tbl fields) values(@var1, @var1)
you only have to define the fields in the table you have vars for.
so in the vba you
can
(disclamer- this is not recommended way, but the fastest execution)
CurrentProject.Connection.Execute ("UpdtTable1 '" &
Trim(strvar) & "',6,'100'")
note: " = string delimiters in vba
'=denotes string delimiters in SQL.

to do this your way you would need a recordset for stagging table so the
select is a join
trick (you can use the view to recreate the SQL then copy the sQL over to
the VBA.)

use the insert into strCurrentTable
( list the fields in strCurrentTable )
values
(staggingtblrs.field1)

 

[Guest]

Thanks Ken

I understand the concept but how do you read field names into code?

Regards

 

[Guest]

BJ

Im only running Access not SQL server ...and everything youve said is
completly over my head ..I think you missed my comment about being an SQL
novice

Thanks for helping anyway

 

[Brendan Reynolds]

You mentioned in another post in this thread that you are "only running
Access, not SQL Server". In that case, you are actually in the wrong
newsgroup, as this group is intended for discussion of issues involving ADPs
and SQL Server.

That said, however, in an MDB, the following would work ...

Public Function ExcludeField(ByVal strFieldName As String, _
ByVal strTableName As String) As String

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim flds As DAO.Fields
Dim fld As DAO.Field
Dim strReturn As String

Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)
Set flds = tdf.Fields
For Each fld In flds
If fld.Name <> strFieldName Then
strReturn = strReturn & fld.Name & ","
End If
Next fld
strReturn = Left$(strReturn, Len(strReturn) - 1)

ExcludeField = strReturn

End Function

Example of use in Immediate window (TestID is the name of the AutoNumber PK
field in tblTest)

? excludefield("TestID","tblTest")
TestText,TestNumber,OldField,NewField

 

[Ken Snell [MVP]]

See Brendan's reply to your post for how to do this.

 

[Guest]

Brendan & Ken,

Thank you both very much for your help

Sorry I posted on the wrong forum - this seemed to be the only forum in the
access section referring to SQL - Which forum would have been the right one?

 

[Ken Snell [MVP]]

perhaps microsoft.public.access.queries

 

[Brendan Reynolds]

microsoft.public.access.queries would be a good group to post SQL questions.
As it happens, this question turned out to be as much about VBA coding as
about SQL, but you couldn't be expected to know that.

If in doubt, you can always post to the general-purpose
microsoft.public.access group

 

[BJ Freeman]

should have read closer. Just assume that post here are about ADP's
sounds like you have the answer you need.