Re: Updating a SQL Dbase using values in specific Excel cells

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for this. The ADP is what the users see. As far as they're
concerned, that is the database. So, everything flows from its use.
The Excel workbook is a quotation system provided by an external source
every quarter - it's protected and all I can do is mess with one bit of
code under a button on one ***. We call up the workbook from a
specific form [Client Update] in the ADP and the code copies specific
values from the SQL database to a '*** in this workbook. The
workbook is modified by the user, and the code prints each '*** as
docs to a PDF printer and saves it under a filename that includes the
unique [Client Ref].

The challenge is to take the values calculated in Sheet1 as part of
this code and feed them back to other fields in the SQL database using
the unique [Client Ref] which is held in Sheet2.("K7") - the active
form comment probably complicated the issue. Apols.

The other point you raised is I guess answered for me above: the user
will expect to see these updated values reflected in the [Client
Update] form after the workbook is 'PDFd'. 'Batch' updating the three
fields in the SQL database as part of the code that performs the
printing routine would be my option.

If I understand you correctly then, 'UPDATE {table name} {fieldlist}
Values{...} WHERE {conditions to identify target row(s)}' is what I
need?

Thanks again.




AA2e72E wrote:
An ADP project in Access is a front end to an SQL Server database. Depending
on its configuration, any changes in the ADP are reflected into the
underlying SQL Server Database automatically.

I am not sure why you are reflecting changes in the work*** to the ADP
rather than to the SQL Server database directly. {If you did, the ADP will
update automatically}.

As I understand it, you are using the Excel GUI to update the table:

The SQL statement may not be straightforward; e.g if you change a value that
is a KEY in the underlying table, the SQL is 'INSERT INTO ...' but if you
change values that are not KEYS in the underlying table. the SQL is 'UPDATE
{table name} {fieldlist} Values{...} WHERE {conditions to identify target
row(s)}'.

The other question you want to ponder is whether you want to reflect the
changes as they happen or whether you want to reflect the changes in batch,
perhaps when a button is clicked or when the workbook is saved.

.

Quantcast