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
pietlinden@hotmail.com wrote:
Quote:
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.
