How do I overwrite table data with a CSV file data?

[john_aspinall]

Here's the overview:

Im building an eCommerce web site using ASP. It has an Access DB at
the back end. Im receiving the Product info from a series of CSV
files. Ive created a Table in Access called 'Products' and the two
fields which concern me are called 'ProductID' and 'Price'. The
product prices change regularly, sometimes quaterly. The updated
prices will be received in the CSV file.

Here's the problem:

I want to somehow get the 'Prices' field in the datbase to be updated
automatically whenver a new CSV file comes in, I dont want to manually
type all the updated prices.

How would I get this done? Can it be done in Access or would I have to
get some sort of ASP program code to do it? Im not a programmer so
would appreciate an 'idiots guide' if poss!

Cheers....John

[pietlinden]

John,

Idiot's guide version? Maybe I'm the wrong guy for that. one way
would be to read the entire file into an empty table, and then run your
update from there via a canned update query. if you wanted the
database to be updated automatically, you could create a form that is
always open and in the OnTimer event, execute the import code.

Here's everything but the timer events:

Option Compare Database
Option Explicit

Public Function GetNewPrices()
On Error GoTo HandleError

'---shut off warnings so the code will run without prompts
DoCmd.SetWarnings False

'---delete any records in holder table because I don't want
leftovers
DoCmd.RunSQL "DELETE * FROM tblNewPriceList;"

'---import the new text file to holder table
DoCmd.TransferText acImportDelim, "NEWPrices Import Specification",
tblNewPriceList, "C:\Documents and
Settings\Piet\Desktop\NEWPrices.txt", False

'---update the current products table with values from the text
file
DoCmd.RunSQL "UPDATE Products INNER JOIN tblNewPriceList ON
Products.ProductID = tblNewPriceList.ProductID SET Products.UnitPrice =
[NewPrice];"

'---delete the text file? or move it?
'---use rename to move the file, Kill to delete it...

'---turn warnings back on.
DoCmd.SetWarnings True

Exit Function

HandleError:
DoCmd.SetWarnings True
MsgBox "Error: " & Err.Number & vbCrLf & "Description: " &
Err.Description, vbOKOnly + vbInformation
Err.Clear
End Function

Okay, before you run off and test this on real data, STOP. This could
potentially munge your work, so test on a BACKUP. (make that mistake
once, and you'll probably never make it again.)

Then you need a _form_ that runs this event on a timer...

Private Sub Form_Timer()
'Show user message...(Getting Prices)
Me.lblFeedback.Visible = True '<---just a label for feedback...
Me.Repaint

'Import all the new data...
GetNewPrices '(calling the function above)

'turn off the label/message, b/c we're done.
Me.Label0.Visible = False
Me.Repaint

End Sub

the form would open when your application opens, but remains hidden.
then just close it when the DB closes. Then your code will run every n
Milliseconds. (So it'll basically watch the folder you specify, so you
should use Rename to move the files that have been un/successfully
imported. The unsuccessful ones you need to check out and retry.

[john_aspinall]

Hi,

Many thanks for the reply - just a couple of questions related to your
answer, to help my understanding of this, sorry if they are a bit basic
as Im not really a programmer, more of a web designer.

When you say read the entire file into an empty table, how would one do
that? Is there a function in Action to do this?

What exactly is a canned update query and where would this run from?

When talking about the import the code, do you mean the CSV file? And
when talking about 'a form that is always open', do you mean a form on
a web page or in Access?

Of all the code youve sent me, where does that go exactly, in an ASP
page?

Thanks in advance....John

you could create a form that is
always open and in the OnTimer event

pi********@hotmail.com wrote:

John,

Idiot's guide version? Maybe I'm the wrong guy for that. one way
would be to read the entire file into an empty table, and then run your
update from there via a canned update query. if you wanted the
database to be updated automatically, you could create a form that is
always open and in the OnTimer event, execute the import code.

Here's everything but the timer events:

Option Compare Database
Option Explicit

Public Function GetNewPrices()
On Error GoTo HandleError

'---shut off warnings so the code will run without prompts
DoCmd.SetWarnings False

'---delete any records in holder table because I don't want
leftovers
DoCmd.RunSQL "DELETE * FROM tblNewPriceList;"

'---import the new text file to holder table
DoCmd.TransferText acImportDelim, "NEWPrices Import Specification",
tblNewPriceList, "C:\Documents and
Settings\Piet\Desktop\NEWPrices.txt", False

'---update the current products table with values from the text
file
DoCmd.RunSQL "UPDATE Products INNER JOIN tblNewPriceList ON
Products.ProductID = tblNewPriceList.ProductID SET Products.UnitPrice =
[NewPrice];"

'---delete the text file? or move it?
'---use rename to move the file, Kill to delete it...

'---turn warnings back on.
DoCmd.SetWarnings True

Exit Function

HandleError:
DoCmd.SetWarnings True
MsgBox "Error: " & Err.Number & vbCrLf & "Description: " &
Err.Description, vbOKOnly + vbInformation
Err.Clear
End Function

Okay, before you run off and test this on real data, STOP. This could
potentially munge your work, so test on a BACKUP. (make that mistake
once, and you'll probably never make it again.)

Then you need a _form_ that runs this event on a timer...

Private Sub Form_Timer()
'Show user message...(Getting Prices)
Me.lblFeedback.Visible = True '<---just a label for feedback...
Me.Repaint

'Import all the new data...
GetNewPrices '(calling the function above)

'turn off the label/message, b/c we're done.
Me.Label0.Visible = False
Me.Repaint

End Sub

the form would open when your application opens, but remains hidden.
then just close it when the DB closes. Then your code will run every n
Milliseconds. (So it'll basically watch the folder you specify, so you
should use Rename to move the files that have been un/successfully
imported. The unsuccessful ones you need to check out and retry.