Code to rename most recently created table?

[Lunatic]

Here's what I would like to do... I have a button that initiates the importing of a file. What I would like is to add to that button so when its clicked, once the file is imported, the new table will be automatically copied into a seperate table and renamed to a 'temp' name that is standard every time.

It wouldn't be difficult if I just deleted the 'temp' table (for lack of a better name), but I'd like to keep a copy of it for archive purposes and the best way I can think of doing this is making a copy of the file at the time it is imported.

Is something like this possible? Can you copy a table based on it being created in the last 60 seconds?

Thank you for any pointers you can provide.

 

[traingamer]

Are you saying you don't know the name of the table you just created?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rjoubert]

Dim objDB As dao.Database
Dim objTD As dao.TableDef

Set objDB = CurrentDb
Set objTD = objDB.TableDefs("OldTableName")

objTD.Name = "NewTableName"

 

[Lunatic]

The name will be date dependent... Its not going to be static. I would like the user to be able to name the table anything they want, but I would like the copy being named a standard name.

For example, importing a file today the table would be named tbl101006 but the copy would be named 'temp'

Then if a file is imported tommorrow the table would be named tbl101106 but the copy would be named 'temp'

Thats what I'm hoping is possible.

 

[rjoubert]

So on 101106, you would overwrite the copy of 'temp' made on 101006. Is that what you intended...to keep only ONE archive?

 

[traingamer]

If the user is entering the table name in a text box on a form, you know the name of the table.

 

[Lunatic]

After today there would be 3 tables...

1 - tblMaster
2 - tbl101006
3 - tbltemp (copy of tbl101006)

after tommorrow there would be 4 tables...

1 - tblMaster
2 - tbl101006
3 - tbl101106
4 - tbltemp (copy of tbl101106)

The uniquely named tables would be retained for archive purposes, but the tbltemp would only be a copy of the most recently imported table (tbl101106).

 

[Lunatic]

Traingamer

You can name a table you are importing before you import it? I thought you could only do that at the end of the import process. If you can name it beforehand in a form that would be pretty easy to do...

 

[traingamer]

I apologize if I was jumping to conclusions. How exactly are you importing the table? You said you were hitting a button. I assumed you had a form and were choosing the input file and so on.

What kind of data are you importing? Is it always the same format? (you can use import specifications)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Lunatic]

No need, I'm usually unclear because I don't know enough to be precise.

I found the code somewhere on tek-tips to run the import function via a button click instead of from the 'file' menu or right-clicking. If there is a better way to do it I'm open to suggestions!

Private Sub Command0_Click()
 RunCommand acCmdImport
  
End Sub

The files being imported will be excel files. Unfortunately it will not be uniform, thats something I argued with the requestor about but lost. Instead I have some fields identified as always being included (a concession on the requestor's part), but the design of the excel file being imported is not necessarily the same.

 

[danvlas]

Select top 1 [Name] from MSysObjects Where [Type]= 1 order by [DateCreate]

will return the name of the most recently created table.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[rjoubert]

Actually, you'll need to add DESC to that query to get the most recent...

Select top 1 [Name] from MSysObjects Where [Type]= 1 order by [DateCreate] DESC

 

[Crowley16]

better yet, why not just create a new table and copy the data to it AS you are importing...

you said you're doing imports with a command button right, so just add a select into statement after the import...

--------------------
Procrastinate Now!

 

[Lunatic]

Crowley - I think thats essentially what I'm trying to figure out how to do. Delete the current tbltemp, import a dated table and create a new tbltemp that is identical to the new dated table.

Is there a way to import a single Excel worksheet into two different tables (one to be named by the user and one that will be automatically be set to tbltemp) simultaneously?

rjoubert, danvlas, Crowley -

How do I get the name this returns:

[blue]Select top 1 [Name] from MSysObjects Where [Type]= 1 order by [DateCreate] DESC[/blue]

into the 'SourceObjectName' in this line:

[purple]DoCmd.CopyObject "tbltemp", acTable, "SourceObjectName"[/purple]

If I can get that I think I'll be nearly there.

Thank you all for your help with the overambitious and undertalented.

 

[rjoubert]

Create a query (let's call it qryNewTable) with the following SQL...

Select top 1 [Name] from MSysObjects Where [Type]= 1 AND [Flags] = 0 order by [DateCreate] DESC

(had to add [Flags]=0 to the query)

Then, in your code...

Dim objrs As Recordset

Set objrs = CurrentDb.OpenRecordset("qryNewTable")

DoCmd.CopyObject "tblTemp", acTable, objRS("Name")

 

[Lunatic]

Sorry rjoubert... I can usually fix problems this small, but I'm running into a wall... I keep recieving a

Run-Time Err 13
Type mismatch

referring to the blue line below

Dim objrs As Recordset
[blue]Set objrs = CurrentDb.OpenRecordset("qryNewTable")[/blue]
DoCmd.CopyObject "tblTemp", acTable, objRS("Name")

I can't figure out where the mismatch is...

Did it have to do with Access modifying the SQL line you provided to the following?

SELECT TOP 1 MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.DateCreate DESC;

 

[PHV]

Dim objrs As [!]DAO.[/!]Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lunatic]

PHV - DAO. didn't work, it created an error (User-defined type not defined), but ADODB seems to no create the error.

rjoubert -

Dim [blue][b]obj[/b][/blue]rs As [red][b]ADODB.[/b][/red]Recordset

Set [blue][b]obj[/b][/blue]rs = CurrentDb.OpenRecordset("qryNewTable")

DoCmd.CopyObject "tblTemp", acTable, [green][b]obj[/b][/green]rs("Name")

When I take out the first two obj it gives me "Compile Error: Sub or Function not defined" on the obj in green.

However when I remove all three obj references (see below) I recieve the same Runtime Error '13' Mismatch type.

Dim rs As Recordset

[highlight][b]Set rs = CurrentDb.OpenRecordset("qryNewTable")[/b][/highlight]

DoCmd.CopyObject "tblTemp", acTable, rs("Name")

I'm probably wasting your time on this, so if there is a book or website you think would help and take less of your time I'm open to suggestions. I really do appriciate your help.

Thank you.

 

[PHV]

User-defined type not defined
You have to reference the microsoft DAO 3.6 object library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886