Insert Into a SQL table via Access 2007

[shairal]

I have created an Access application for my end users to add and update data
in a table in SQL server (2000). Currently they use a form to update or add
individual items of data to the SQL table and all works great. However, I
have had a request to create a way to do a mass update, say from an Excel
file, to the SQL table.

I have linked to my Excel file in Access and tried an Append query to append
the data to my SQL table, but I think my primary key field, which is an
identify seed in my SQL table is messing me up. I looked at Pass Through
queries, but I cannot get it to recognize my linked Excel table in the Access
db.

Is there a way to do a mass update via Access to a SQL table with an
identity seed set? Any help would be greatly appreciated!!

 

[John W. Vinson]

I have created an Access application for my end users to add and update data
in a table in SQL server (2000). Currently they use a form to update or add
individual items of data to the SQL table and all works great. However, I
have had a request to create a way to do a mass update, say from an Excel
file, to the SQL table.

I have linked to my Excel file in Access and tried an Append query to append
the data to my SQL table, but I think my primary key field, which is an
identify seed in my SQL table is messing me up. I looked at Pass Through
queries, but I cannot get it to recognize my linked Excel table in the Access
db.

Is there a way to do a mass update via Access to a SQL table with an
identity seed set? Any help would be greatly appreciated!!

See the VBA help for Identity_Insert. You'll probably need to do it as a
Passthrough query, so I'm not sure how to get the Excel data visible to SQL...
but the Identity_Insert will let you overwrite the IDENTITY field. Use with
caution!

 

[Paul Shapiro]

If your Excel file doesn't have an ID value, then you can do the append
without specifying the ID field. Look at the Append syntax to see how to
specify the fields you are supplying values for.

The other possible issue is that you probably can't do this as a
pass-through query unless you link the Excel file to the SQL Server. It's
Access that has the connection to the Excel data, so the append query should
probably run as an Access query and not as a pass-through to the SQL Server.

 

[John W. Vinson]

If your Excel file doesn't have an ID value, then you can do the append
without specifying the ID field. Look at the Append syntax to see how to
specify the fields you are supplying values for.

The other possible issue is that you probably can't do this as a
pass-through query unless you link the Excel file to the SQL Server. It's
Access that has the connection to the Excel data, so the append query should
probably run as an Access query and not as a pass-through to the SQL Server.

I may need to be dealing with this scenario myself soon... what I'm not sure
is how to set IdentityInsert (in T/SQL) *and* run a DAO linked query. If I
figure it out I'll post back, or if anyone knows I'd be interested!

 

[shairal]

Yes, please post back, I would be interested in knowing.

My Excel file does not have an ID value; I have tried just adding the field
and leaving it blank but I get something like “cannot insert due to nullsâ€
which of course my identity field is a non-null field. Though if the end
user add one part at a time, the identity field completes as expected.

I’ll check out the Identity_Insert in VBA (though my skill set in VBA is
very limited) so your warning makes me a little nervous.

Thanks for all your advice! It’s much appreciated.

 

[shairal]

Yes, please post back, I would be interested in knowing.

My Excel file does not have an ID value; I have tried just adding the field
and leaving it blank but I get something like “cannot insert due to nullsâ€
which of course my identity field is a non-null field. Though if the end
user add one part at a time, the identity field completes as expected.

I’ll check out the Identity_Insert in VBA (though my skill set in VBA is
very limited) so your warning makes me a little nervous.

Thanks for all your advice! It’s much appreciated.

 

[Paul Shapiro]

IdentityInsert is a session (connection) setting. From SQL BOL: "At any
time, only one table in a session can have the IDENTITY_INSERT property set
to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, SQL Server returns
an error message that states SET IDENTITY_INSERT is already ON and reports
the table it is set ON for. If the value inserted is larger than the current
identity value for the table, SQL Server automatically uses the new inserted
value as the current identity value."

As long as you use the same connection for the Set Identity_Insert statement
as the DAO query, I think the setting remains enabled on that connection
until you issue a set command turning it off.

 

[Paul Shapiro]

You don't need the identity insert statement if you're not supplying a value
for the identity column. Your insert query should specify the fields you are
inserting, and omit the PK field. Something like:

Insert Into dbo.MyTable
(field2, field3, field4)
Select ... From LinkedExcelPseudoTableName Where ...

The (field2, field3, field4) is the list of SQL table field names for which
you are assigning data, and the Select statement provides those columns and
those columns only, in the same order as the field listing.

If the SQL PK field has the identity property, a new value will be assigned
automatically by SQL Server.

 

[John W. Vinson]

As long as you use the same connection for the Set Identity_Insert statement
as the DAO query, I think the setting remains enabled on that connection
until you issue a set command turning it off.

Thanks, Paul. I'll experiment with this. I presume that running a passthrough
query to do nothing but set it, then a DAO query, then another passthrough to
clear it should do the trick?