Saving FileName Do.cmd?

[bbrendan]

Hi All,

I have a question I hope you can help with.
I use the
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "IMPORT_STAGING_TABLE", Me!txtOpenFile, True, ""

I grab this file using an dialogue box.
What im wondering is how do I just save the filename?. As I would like to save this to a new table, just so I can run a check against it next time to ensure they dont use the same file again!.

Any help would be fantastic.

Thanks
brendan

 

[AlanS]

A simple way to save just a file name (or any other bit of data) to a table, and later retrieve it, would be via DAO. For instance, you could set up a table called SaveData, consisting of only a single Text field called SaveText, and use the following two procedures to (1) clear any existing records and create a new one, and (2) retrieve the value in the first (and presumably only) record. With a little more code, you could also accommodate multiple records, perhaps by adding a DataKey field to uniquely identify separate named pieces of data.

A more elegant solution is to create, modify, read and delete custom database properties; I believe there is sample code in Microsoft's Knowledgebase for those tasks, and in any case I have routines to do so, which I'll happily e-mail to you if you're interested.

Sub WriteIt(sData as String)
Dim dbs as Database, rst as Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ("SaveData")
'clear any existing records
Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
'create new record
rst.AddNew
rst!SaveText = Nz(sData, "")
rst.Update
set rst = Nothing
set dbs = Nothing
End Sub

Function ReadIt() as String
Dim dbs as Database, rst as Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ("SaveData")
If rst.EOF Then
ReadIt = ""
Else
ReadIt = rst!SaveText
End If
set rst = Nothing
set dbs = Nothing
End Function