Automated Import Not Working Properly 2

dragongunner0351 · May 28, 2006

Hello all,
I have a form named frmAgedReceivables, the data source for the form is a table named tblAgedReceivables.

Everyday I have been importing external data (a .xls file) to refresh the listing of past due customers. I have been wanting to automate this task so I created a MACRO to import the data.

I only want this import to take place once a day (to update the tblAgedReceivables) but if I put it in the onload or onopen events of the frmAgedReceivables it will want to load the spreadsheet each time. (not desirable)

Does anyone know of a way to automate this import so that it will import only once to the table or a way to allow for it to import how ever many times a day the form is loaded but to only refresh the tblAgedReceivables if a change in the .xls was detected?

All help is appreciated.

Eupher · May 28, 2006

Hmmm...

First I will offer a suggestion: don't use macros. Or at least don't use both macros and VBA in your DB. But it's pretty hard to keep VBA out of your project - even the simplest command button uses code. VBA has one HUGE advantage over macros - error handling. A lot easier to debug, too.

Now, on to your question. Probably lots of ways to do it, here's just one. Make a little table to hold a single value: the date the spreadsheet was last imported. Then in your form's open event, compare that date to today's date. If they are not equal, import the data and write today's date to the table, so when the form is loaded again on the same day the import doesn't happen again.

HTH,

Ken S.

TheAceMan1 · May 28, 2006

How are ya dragongunner0351 . . .

I was think along the same lines as [blue]Eupher[/blue] but instead of using a table I was think of using a [blue]Custom DB or Table property[/blue]. This would save the overhead of the table.

[blue]Your thoughts?[/blue]

See Ya! . . . . . .

dragongunner0351 · May 28, 2006

Thanks Eupher and TheAceMan1, Obviously I would like to keep things as simple and streamlined as possible. I'm not sure I understand the Custom DB or Table property. Euphers suggestions seems like something I can wrap my brain around, but like I said earlier simple is better right now. Could I get a little 101 on the Custom DB or Table Property?

Thanks to you both

Eupher · May 28, 2006

Hi, AceMan,

Good idea. Whaddya think, could the Tag property of the form work for this? I've never used custom properties before, either...

Ken S.

TheAceMan1 · May 29, 2006

Roger That dragongunner0351 & Eupher . . .

1st . . . [blue]Eupher's[/blue] idea of the [blue]Tag[/blue] property is excellent. Only problem is [blue]form control properties can only be saved in design view![/blue]

DB properties to the rescue! . . . Like any object a DB has properties and unlike most objects you can create your own [blue]custom properties[/blue]. You give them a name and data type, and can call on them anywhere in your code. The properties are read/write nonvolatile and become a permament part of the DB!

[blue]dragongunner0351[/blue]: You could for instance use an [blue]AutoExec[/blue] macro to test the property and load the data when the DB opens!, or you may want to insure you have the latest data before you print.

Just remember that these custom properties are not a catch-all, if you'll have enough of them, better to make a table (already suggested by [blue]Eupher[/blue]).

So . . . lets get on with the code. In a new module in the modules window, copy/paste the following:

Code:

[blue]Public Function CreateDBProp(prpName As String, prpDatTyp As Long, _
                             prpVal) As Boolean
   [green]'prpDatTyp: dbBoolean, dbByte, dbCurrency, dbDate, dbDecimal, dbDouble,
   '           dbFloat, dbInteger, dbLong, dbMemo, dbSingle, dbText[/green]
   Dim db As DAO.Database, prp As Property
   Dim Msg As String, Style As Integer, Title As String
   
On Error GoTo GotErr
   If IsEmpty(dbProp(prpName)) Then
      Set db = CurrentDb
      Set prp = db.CreateProperty(prpName, prpDatTyp, prpVal)
      db.Properties.Append prp
      CreateDBProp = True
   Else
      Msg = "The property '" & prpName & "' Already Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Create . . . Property Exists!"
      MsgBox Msg, Style, Title
   End If
   
SeeYa:
   Set prp = Nothing
   Set db = Nothing
   Exit Function
   
GotErr:
   Call PropErrMsg
   Resume SeeYa
      
End Function

Public Function dbProp(prpName As String)

On Error GoTo GotErr
   dbProp = CurrentDb.Properties(prpName)
   Exit Function
GotErr:
   If Err.Number <> 3270 Then Call PropErrMsg
   
End Function

Public Function SetDBProp(prpName As String, prpVal) As Boolean

On Error GoTo GotErr
   Dim Msg As String, Style As Integer, Title As String
   
   If IsEmpty(dbProp(prpName)) Then
      Msg = "The property '" & prpName & "' Doesn't Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Property Doesn't Exists! . . ."
      MsgBox Msg, Style, Title
   Else
      CurrentDb.Properties(prpName) = prpVal
      SetDBProp = True
   End If
   
SeeYa:
   Exit Function
   
GotErr:
   Call PropErrMsg

End Function

Public Function DelDBProp(prpName As String) As Boolean

On Error GoTo GotErr
   Dim db As DAO.Database, prp As Property
   Dim Msg As String, Style As Integer, Title As String
   
   If IsEmpty(dbProp(prpName)) Then
      Msg = "The property '" & prpName & "' Doesn't Exists!"
      Style = vbInformation + vbOKOnly
      Title = "Property Doesn't Exists! . . ."
      MsgBox Msg, Style, Title
   Else
      Set db = CurrentDb
      Set prp = db.Properties(prpName)
      db.Properties.Delete (prp.Name)
      DelDBProp = True
   End If

   
SeeYa:
   Set prp = Nothing
   Set db = Nothing
   Exit Function
   
GotErr:
   Call PropErrMsg
   Resume SeeYa
      
End Function

Public Sub PropErrMsg()
   Dim Msg As String, Style As Integer, Title As String
   
   Msg = "Error " & Err.Number & ": " & Err.Description
   Style = vbCritical + vbOKOnly
   Title = "System Error! . . ."
   MsgBox Msg, Style, Title

End Sub

Public Sub PrintDBProps()

On Error GoTo GotErr
   Dim db As DAO.Database, prp As DAO.Property
   Dim Msg As String, Style As Integer, Title As String
   
   Set db = CurrentDb()
   
   For Each prp In db.Properties
      If prp.Name <> "Connection" Then
         Debug.Print prp.Name & " = " & prp
      End If
   Next
   
SeeYa:
   Set prp = Nothing
   Set db = Nothing
   Exit Sub
   
GotErr:
   Msg = "Error " & Err.Number & ": " & Err.Description
   Style = vbInformation + vbOKOnly
   Title = "System Error! . . ."
   MsgBox Msg, Style, Title
   
   Resume Next
      
End Sub[/blue]

To create a custom property, execute the following in the immediate window (since this is a one time affair):

Code:

[blue]? CreateDBProp("Name", DataType, InitialValue)

Example:
? CreateDBProp("LastXLSUpdate", dbDate, Date)[/blue]

[tt]The data types are: dbBoolean, dbByte, dbCurrency, dbDate, dbDecimal, dbDouble,
dbFloat, dbInteger, dbLong, dbMemo, dbSingle, dbText[/tt]

[purple]Note: you can't assign a [blue]Null[/blue] or a [blue]Null String[/blue] to a custom property![/purple]

The function will return true (-1) if the property was successfully created.

With the property created now your off an running. To set the property:

Code:

[blue]   Call SetDBProp("Name", Value)

Example:
   Call SetDBProp("LastXLSUpdate", #5/12/2000#)[/blue]

Finally to access the property:

Code:

[blue]   variable = dbProp("LastXLSUpdate")

or

   variable = CurrentDb.Properties("LastXLSUpdate")[/blue]

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

TheAceMan1 · May 29, 2006

dragongunner0351 . . .

So sorry, didn't forget your problem. General code your looking for would be something like:

Code:

[blue]   If dbProp("LastXLSUpdate") <> Date Then
      [green]'Import Here![/green]
      Call SetDBProp("LastXLSUpdate", Date)
   End If[/blue]

See Ya! . . . . . .

Eupher · May 29, 2006

Hi, AceMan,

Nice work that definitely deserves a star! I'm betting you had that in your library, I hope you weren't up all night working on it. The really beautiful thing is it's ready to go, just cut and paste into a module. Well done.

Happy Memorial Day to all.

Ken S.

TheAceMan1 · May 29, 2006

Eupher said:
[blue] . . . I'm betting you had that in your library . . .[/blue]

Yep! . . . been using it since the birth of 2K.

[blue]Happy Memorial Day back! . . .[/blue]

See Ya! . . . . . .

Eupher · May 29, 2006

Heh,

Now it's in *my* library... ;-)

Ken S.

TheAceMan1 · May 29, 2006

Eupher . . .

Been thinking about making it an FAQ . . .

. . . and what happened to [blue]dragongunner0351 ?[/blue]

See Ya! . . . . . .

dragongunner0351 · May 29, 2006

The AceMan Cometh, to the both of you thank you for all your help. I really appreciate what both of you have done. My apologies for not keeping up with the thread. My family is a military one and today we remember past, present and future.

AceMan I hope you weren't up all night with that code Eupher is correct you deserve a star. You'll get one from me.

To all my brothers
SEMPER FI!!

dragongunner0351 · May 29, 2006

Hello again I copied the code into a new module that I named DBProp. Where do I call this code from? Also, the last four pcs. of code are those run from the form??

Thank you in advance

TheAceMan1 · May 29, 2006

dragongunner0351 . . .

Post the code for opening the form . . .

Post the code for calling your import macro . . .

See Ya! . . . . . .

dragongunner0351 · May 29, 2006

I'll give it a go

Thank you

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Automated Import Not Working Properly 2

dragongunner0351

Programmer

Eupher

MIS

TheAceMan1

Programmer

dragongunner0351

Programmer

Eupher

MIS

TheAceMan1

Programmer

TheAceMan1

Programmer

Eupher

MIS

TheAceMan1

Programmer

Eupher

MIS

TheAceMan1

Programmer

dragongunner0351

Programmer

dragongunner0351

Programmer

TheAceMan1

Programmer

dragongunner0351

Programmer

Similar threads

Part and Inventory Search

Sponsor