timer- to know time of last action....

[Shivalee Gupta via AccessMonster.com]

i am a newbie.please answer me step-by-step.
I have a form on which i have a command button.this command button imports a particular file from excel to access regularly and oftenly, as i keep updating the excel file and then i have to import again. (i have 25 import buttons as i have 25 tables & 25 excel files).
i need to write a code which will tell me when was the last time i imported that file into access-so whether the excel file was changed or not and accordingly whether i want to import again or not.
thanks a lot...
shivalee

 

[Nikos Yannacopoulos]

Shivalee,

Here's one way to do it, in broad terms:

Make a two field table called, say, tblLastImport, with fields:
fldTableName (text)
fldLastImported (date/time)

This table will be used to store the date/time of the Excel file that
was last updated (imported) for each table. Then the code behind the
command button would be something like:

Dim fldr, fl
Dim tblname As String, extfile As String
Dim lstmodif As Date, lstimp As Date

tblname = "TargetTableName"
extfile = "C:\SomeFolder\SomeWorkbook.xls"

Set fldr = CreateObject("Scripting.FileSystemObject")
Set fl = fldr.GetFile(extfile)
lstmodif = fl.DateLastModified
lstimp = DLookup("[fldLastImported]","tblLastImport","[fldTableName]='"
& tblname & "'")
If lstmodif > lstimp Then
'your code to carry out the import
strSQL = "UPDATE tblLastImport SET fldLastImported = "
strSQL = strSQL & lstmodif & " WHERE fldTableName = '"
strSQL = strSQL & tblname & "'"
CurrentDb.Execute strSQL
Else
msgbox "No new data found",vbExclamation, "Import aborted"
End if


Obviously, instead of putting this code behind each and every of the 25
buttons, you can put it in a sub and call it from each button, passing
the table and file names as parameters.

The next step in improving your user interface / design, would be to use
a single button for the job, together with a listbox (or combo) for
the user to select which table to update!

HTH,
Nikos

 

[Shivalee Gupta via AccessMonster.com]

can u clarify 2-3 things:
1. why do need to create a table called tblLastImport?
2. what do i fill in this:
tblname = "TargetTableName"
extfile = "C:\SomeFolder\SomeWorkbook.xls"

correct me if i am wrong... here tblname is the name of the access table i have imported.
and extfile is the name of the excel file i have to import.
3. can these both names be same?
i have already thanked you a lot for the transferspreadsheet help.
thanks in advance for this too.
shivalee

 

[Nikos Yannacopoulos]

Shivalee,

See my answers below.

HTH,
Nikos

can u clarify 2-3 things:
1. why do need to create a table called tblLastImport?

So you can store the date/time of the last update per table (actually
the date/time the corresponding spreadsheet was last updated). You need
this in order to determine whether the spreadsheet on your disk is the
one you already imported the last time, or a newer one (that's what the
If / Then / Else structure in the code checks).

2. what do i fill in this:
tblname = "TargetTableName"

Name of Access table to import into.

extfile = "C:\SomeFolder\SomeWorkbook.xls"

Name of Excel file to import.

correct me if i am wrong... here tblname is the name of the access table i have imported.
and extfile is the name of the excel file i have to import.

As above.

3. can these both names be same?

Yes and No... You can use the same name for the table and the file, but
the name of the table is just the name as you see it in the database
window, while the name of the file will need to have the full path at
the beginning and the .xls extension at the end; so even if the filename
itself is the exact table name, the two variables will always be
assigned different values in the code. Of course, if you know they wil
always be the same, and the folder will always be the same, you could
"construct" the second variable based on the first, like:
tblname = "TargetTableName"
extfile = "C:\SomeFolder\" & tblname & ".xls"

i have already thanked you a lot for the transferspreadsheet help.
thanks in advance for this too.

Glad to have helped!

 

[Shivalee Gupta via AccessMonster.com]

i have copy and paste your code in the on_click of my command button.
Made a two field table called, timer_Last_Import, with fields:
Table_Name (text)
Last_Imported (date/time)

i am getting an error at this line:
lstimp = DLookup("[last_imported]", "Timer_last_import", "[table_Name]='" & tblname & "'")
run time error 94.
invalid use of null.
what am i doing wrong?

i have also replaced names in:
strSQL = "UPDATE timer_Last_Import SET Last_Imported = "
strSQL = strSQL & lstmodif & " WHERE Table_Name = '"
strSQL = strSQL & tblname & "'"

if you'll say i'll send the code i have changed.

sorry to bother you so much...
regards,
shivalee

 

[Nikos Yannacopoulos]

Shivalee,

The only reason I can think of why you get this error message is
variable tblname is null at the time, ehich means somethign's gone wrong
while assigning it a value. To check, put a break on the paricular code
line (cursor in line, menu Debug > Toggle Breakpoint) so the execution
pauses there, and run; the execution will pause and the line will be
highlighted, at which point you can hover the cursor over variable
tblname (or any other!) and see its current value. Alternatively, you
can insert a line:
Debug.Print tblname
just before the line that gives you the problem, and the code wil print
the variable's value in the immediate window (Ctrl+G to open).
This way you van check if this is indeed the problem, and once verified
you can start looking at what's gone wrong with assigning it a value.

The strSQL expression looks gine as far as I can tell.

If your problem is not solved and you post back, it would be a good idea
to post the whole piece of code.

Regards,
Nikos

 

[Shivalee Gupta via AccessMonster.com]

i did:menu Debug > Toggle Breakpoint
then i run, then i saw all values, lstmodif also shows a date value and lstimp also show a date value, tblname is showing me usr40 and extfile is showing me full path.
still it is showing the same error.
run time error 94.
invalid use of null.

my whole piece of code is:
Private Sub Command27_Click()
Dim fldr, fl
Dim tblname As String, extfile As String
Dim lstmodif As Date, lstimp As Date

tblname = "usr40"
extfile = "D:\Documents and Settings\shivaleegupta\Desktop\Shivalee\barun project\database\usr40.xls"

Set fldr = CreateObject("Scripting.FileSystemObject")
Set fl = fldr.GetFile(extfile)
lstmodif = fl.DateLastModified
lstimp = DLookup("[last_imported]", "Timer_last_import", "[table_Name]='" & tblname & "'")
If lstmodif > lstimp Then
'your code to carry out the import
strSQL = "UPDATE timer_Last_Import SET Last_Imported = "
strSQL = strSQL & lstmodif & " WHERE Table_Name = '"
strSQL = strSQL & tblname & "'"
CurrentDb.Execute strSQL
Else
MsgBox "No new data found", vbExclamation, "Import aborted"
End If
End Sub


i dont know if this is what you meant by whole piece of code.
please keep in touch, dont leave in middle of code.
thanks a lot.
shivalee

 

[Nikos Yannacopoulos]

Shivalee,

If you can see a value for lstimp then the error does not occur in this
line; the line executes correctly or lstimp would not have a value!
I replicated this in a test database and found there was a problem - an
oversight on my part, apologies - in the strSQL expression: I should
have delimited the date variable with #'s. Change the code part to:

strSQL = "UPDATE timer_Last_Import SET Last_Imported = #"
strSQL = strSQL & lstmodif & "# WHERE Table_Name = '"
strSQL = strSQL & tblname & "'"

This way it works for me.

Now, once you have cracked this, you will need to replace:
'your code to carry out the import
with the actual code (or call to it) that carries out the import.

If you're still having problems, you are welcome to e-mail me your
database so I can have a look if you want... just make sure it isn't too
big: compact, zip, remove mass of data leaving just sample, if too much
data already in. I have a slow connection!

HTH,
Nikos

 

[Shivalee Gupta via AccessMonster.com]

hello nikos,
it still shows me an error at
lstimp = DLookup("[last_imported]", "Timer_last_import", "[table_Name]='" & tblname & "'")
and its the same error:

run time error 94.
invalid use of null.

can you send me your e-mail id so i can send you my database.
it is very nice of you to help me.
regards,
shivalee

 

[Nikos Yannacopoulos]

Shivalee,

My address appears next to my name, and should come up automatically if
you choose to reply by mail, but you have to do the obvious with it. In
case you can't see it in your news reader, then the first part is nyannaco
then the symbol for at, then
in dot gr
with the symbol instead of the word dot. Sorry for the riddle, but if I
post my exact address without anti-spam measures I'm in for big trouble!

Nikos

 

[Nikos Yannacopoulos]

Well, sometimes the obvious is the hardest one to see! The problem is
the code assumes there is already a record for the table in
Timer_last_import, so the DLookup returns the associated last_imported
value. You didn't have a record so the DLookup returned Null. Just
create a dummy record manually, with a date like 01/01/1980, for
instance, and it will solve the problem.

Nikos

Shivalee,

My address appears next to my name, and should come up automatically if
you choose to reply by mail, but you have to do the obvious with it. In
case you can't see it in your news reader, then the first part is nyannaco
then the symbol for at, then
in dot gr
with the symbol instead of the word dot. Sorry for the riddle, but if I
post my exact address without anti-spam measures I'm in for big trouble!

Nikos

hello nikos,
it still shows me an error at lstimp = DLookup("[last_imported]",
"Timer_last_import", "[table_Name]='" & tblname & "'")
and its the same error:

run time error 94.
invalid use of null.

can you send me your e-mail id so i can send you my database.
it is very nice of you to help me.
regards,
shivalee