Updating a SQL Dbase using values in specific Excel cells

  • Thread starter Thread starter M600
  • Start date Start date
M

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
 
M600 said:
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
Click to expand...

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
 
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
Click to expand...

Oops forgot the little quotes

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

Sorry about that
 
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.
 
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.
 
Back
Top