Updating a SQL Dbase using values in specific Excel cells

[M600]

Hi
Forgive any newbie gaffs - my first post

I have a task to 'push' 3 specific values from an Excel spreadsheet
into an Access .adp-fronted SQL database. The Access project updates a
generic Excel spreadsheet with Client information before the open
spreadsheet is saved with a new name, at which point these values need
to feed back into the database. As the spreadsheet is initially always
the same one, the values change but the 'sheet/cells/refs are
consistent.

In the [Clients] table, I need to match a unique [Client Ref] field,
held in Sheet1.("D6"), and update fields: [Org Amount], [Org Comm] and
[PPP] which will be variously empty and populated.

If there's a way of utilising the Access form, this is kept open and
active when the spreadsheet is created.

Also, can anyone help me with the syntax for this:
sSQL = "DELETE FROM DocsSummary WHERE [UniqueID]=????",
where ??? is the Excel cell reference for the matching [UniqueID]
value, held in cell F41?

I guess this is all a walk in the park for someone. I've had some
breakthroughs with a test Access mdb but I'm really struggling with
SQL. All help greatly appreciated.
Thanks
Dino

 

[Stopher]

Hi
Forgive any newbie gaffs - my first post

I have a task to 'push' 3 specific values from an Excel spreadsheet
into an Access .adp-fronted SQL database. The Access project updates a
generic Excel spreadsheet with Client information before the open
spreadsheet is saved with a new name, at which point these values need
to feed back into the database. As the spreadsheet is initially always
the same one, the values change but the 'sheet/cells/refs are
consistent.

In the [Clients] table, I need to match a unique [Client Ref] field,
held in Sheet1.("D6"), and update fields: [Org Amount], [Org Comm] and
[PPP] which will be variously empty and populated.

If there's a way of utilising the Access form, this is kept open and
active when the spreadsheet is created.

Also, can anyone help me with the syntax for this:
sSQL = "DELETE FROM DocsSummary WHERE [UniqueID]=????",
where ??? is the Excel cell reference for the matching [UniqueID]
value, held in cell F41?

I guess this is all a walk in the park for someone. I've had some
breakthroughs with a test Access mdb but I'm really struggling with
SQL. All help greatly appreciated.
Thanks
Dino

sSQL = "DELETE FROM DocsSummary WHERE UniqueID = " & range("F41")

As for uploading back to the DB I have no idea, Guess if it had a
unique ID you could run an UPDATE statement.

Stopher

 

[Stopher]

sSQL = "DELETE FROM DocsSummary WHERE UniqueID = " & range("F41")

As for uploading back to the DB I have no idea, Guess if it had a
unique ID you could run an UPDATE statement.

Stopher

Oops forgot the little quotes

sSQL = "DELETE FROM DocsSummary WHERE UniqueID = '" & range("F41")&"'"

Sorry about that

 

[Guest]

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 worksheet 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.

 

[M600]

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 sheet. 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 'sheet in this workbook. The
workbook is modified by the user, and the code prints each 'sheet 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.