Using Access table to update fields in SQL

[mcnmpc]

Got an Access database here for Stock parts and need to find a way to import the current amounts to our Production program which is based on SQL.

What is the best way to change a SQL field using data from an Access database table? I do not want to replace the whole table just change some of the fields using the Part ID which is the same on both databases.

 

[JayKusch]

I would, for ease, create a physical temp table within Enterprise Manager w/ the fields of PartID and Amount. I would then create an index on the PartID.

Then right-click on the new temp table and choose All Tasks..Import Data. Set your SOURCE as the AccessDB table, set you DESTINATION as your SQL Server temp table.

Now use a SELECT statement to pull the ProductID and Amount out of the AccessDB to import into your new temp table.

Once it is imported, you will create an UPDATE statement that joins your temp table w/ the production table on the ProductID.

Then set the Amount field of the production table to equal your amount in the temp table.

DONE!

Thanks

J. Kusch

 

[mercurymik]

I would link the SQL Server table to the Access database using an ODBC connection. As long as your ODBC connection is authenticated to a user with the proper authority in the SQL Server, you can use your Access queries to update the data.

I suggest you setup a user on the SQL Server that only has permissions to the tables you want to update. Then use that user as the ODBC authentication. If you have very few users that have to log into a network to use the Access Database, it is better to add the user to the SQL Server Logins and assign them to a Role that only has the neccessary authority. Then when you setup the ODBC you can use Windows Authentication.

 

[mcnmpc]

To be clear we want to stop using the Access database as this was created instead of people using the production program.

Just need to transfer the data over to the production program and stop using the Access program.

Need to find the matching Part number and update the Qty amounts. The part number is already in the SQL table.

 

[mercurymik]

Either way, you can link the SQL server table to the Access database and use Access queries to perform the updates. You just have to be sure a key is defined. When linking the table, if access doesn't pick up the key it will ask you to select fields to create unique records.

You can also use the "Upsizing Wizard" (Access2000) under Tools - Database Utilities to copy your existing table into the SQL Server. Then use the SQL Server Query Analyzer to write and run the code to update your table.

You can even create a query of the Stock ID and Quantiy in Access and upsize only the query to the SQL Server.

There are many ways to accomplish what you want, the one thing is, it is much easier to have the two tables in the same place when you perform your update.

Have a great day.