Renaming Imported File Automatically

[tom_crisp]

Hi guys, thanks for all the help recently!

I have a button on a form that imports a file, and another that runs a load of queries on a named table.

What I want to be able to do is automatically rename the file that is imported to 'tbl_full' so that the queries then have something to work on.

The problem is that the file imported will not always have the same filename. However the first two letters will be the same.

Perhaps there is a way to use sql 'LIKE' or similar?
---
OR
----
This needs to go into the onclick command for a button.. eg:
bold = what i want, italic = what i have.

Private Sub cmdImport_Click()
dim name as string
On Error Resume Next
'open import file dialog box
DoCmd.RunCommand acCmdImport = name
table with name = name becomes tbl_full

End Sub

 

[Norie]

Why not just import to an existing table?

 

[tom_crisp]

This system will be used many times, so I dont know if it will work.

Do you mean that I should keep the skeleton of the table there all the time, and just delete the data from it when one run through is complete?

I shall try this and see what happens.

EDIT:

This is an OK solution, just requires me to add a bit of guidance for the users.

Thanks for your help.

 

[mdmilner]

Considering you already have something working, you probably shouldn't bother with this suggestion.

One of my preferred techniques is to store information in tables related to the current status of any application. This is a bit vague so I'll explain.

Lets say you import some data and it comes in as tableX.
You'd then go to a table and save the name tableX.
Your application would then use a SQL statement to extract that table to use elsewhere in the application, including dynamically generating new queries that use that new tableX name.

Where this gets interesting is, lets say you import a new table each day with a different unique name. You could use one field that has something like "imported_table" in it and then the next is the name tableY. There could be other entires for tableW, tableZ and tableB. You could use a SQL statement to extract a list of those tables.

One use of something like this is if you were to build a combo or listbox in a form, you could generate a current list of all imported tables. The same form could have your button on it - you'd tell your form that when that button is clicked, please run your queries on the selected table (in the list or combo box).

Take it a step further. In another record, store the value "last_selected" and then the name of the table selected. When the form is first opened, have it go get that last_selected value and then have the form select it. ie, you're now carrying forward information done the last time it was opened to a new instance.

This could easily be adapted to an automated import process that just happens to re-assign that last_selected value.

Take it another step further - adaptable default tables. Use at least a 3 table one-to-many relationship with the top table have an index, a field with "table_list" and then it refers to a 2nd table that holdeds an index & then one record per unique table. A third table could hold user defined properties of that table (in another one-to-many relationship) such as details that describe it. What days it covers, it's purpose, whatever you need.

What I'm really getting at is - there probably is no way you need something this complex for something that pretty much already works. However, there is a reason why some complex suggestions are made here - and it's things like this. Something very simple like storing a single detail for use in an application can be carried forward into something much more complex and versatile. I'll add that just about everything I've written in the last few years uses some variant of this technique.

Mike