Identify and rename MS Access latest table - SQL (1 Viewer)

Rakesh935

Registered User.
Local time
Today, 17:01
Joined
Oct 14, 2012
Messages
71
Hi All,

I have a .mdb file which has about 20 tables in which I need to rename the latest created table.

Now to identify the latest created table I am using the below query:

SELECT a.Name, a.ID, a.DateCreate
FROM MSysObjects AS a INNER JOIN (SELECT Max( DateCreate) as Latest
FROM MSysObjects
Where Flags = 0) AS b ON a.DateCreate = b.Latest;

which is successful but I am unable to rename the table as 'TableName_mm/dd/yyyy' through SQL.

Request for advise/help.

Thanks,
Rakesh
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:31
Joined
May 7, 2009
Messages
19,175
using VBA:
Code:
Public Function fncLatestTable()
Dim sSQL As String
Dim sFile As String
Dim dFile As Date
Dim db As DAO.Database
Dim td As DAO.Tabledef

sSQL = "SELECT a.Name, a.ID, a.DateCreate " & _
"FROM MSysObjects AS a INNER JOIN (SELECT Max( DateCreate) as Latest " & _
"FROM MSysObjects " & _
"Where Flags = 0) AS b ON a.DateCreate = b.Latest;"

Set db = Currentdb
With db.Openrecordset(sSQL)
    sFile = .Fields(0).Value
    dFile = .Fields(1).Value
End With
Set td = db.TableDefs(sFile)
td.Name = sFile & "_" & Format(dFile, "mm\/dd\/yyyy")
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set td = Nothing
Set db = Nothing
End Function
 

isladogs

MVP / VIP
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
18,186
Using special characters such as '/' in a table (or field) name is a bad idea
Instead of TableName_mm/dd/yyyy, better to use TableName_mmddyyyy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
21,358
Also, just another option for renaming objects, not just tables:
Code:
DoCmd.Rename NewName, acObjectType, OldName
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:31
Joined
Feb 19, 2013
Messages
16,553
although your query works, an alternative one would be

Code:
SELECT TOP 1 [Name], ID, DateCreate
FROM MSysObjects
Where Flags = 0
ORDER BY DateCreate Desc

note that Flags=0 could also potentially bring through other objects such as modules, queries etc. Better to use [Type]=1 for local tables or =6 for linked tables
 

isladogs

MVP / VIP
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
18,186
although your query works, an alternative one would be

Code:
SELECT TOP 1 [Name], ID, DateCreate
FROM MSysObjects
Where Flags = 0
ORDER BY DateCreate Desc

note that Flags=0 could also potentially bring through other objects such as modules, queries etc. Better to use [Type]=1 for local tables or =6 for linked tables

Or Type = 4 for linked ODBC tables such as SQL Server.
I would filter using both Type and Flags as if you don't specify Flags=0, there is a possibility the latest created table could be a system table added as a result of an action you've taken e.g. save an export/import specification
 

Mark_

Longboard on the internet
Local time
Today, 04:31
Joined
Sep 12, 2017
Messages
2,111
This may sound like an odd question, but why do you need to rename a table? From the format, it looks like you are saving data as part of a table name.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 19, 2002
Messages
42,981
Whew. I din't think anyone would notice the real problem.
 

Rakesh935

Registered User.
Local time
Today, 17:01
Joined
Oct 14, 2012
Messages
71
This may sound like an odd question, but why do you need to rename a table? From the format, it looks like you are saving data as part of a table name.

This is gonna be a tracking mechanism by dates. And since the nature of the data would be incremental therefore it would be ideal to use the latest table for further processing.
 

Rakesh935

Registered User.
Local time
Today, 17:01
Joined
Oct 14, 2012
Messages
71
using VBA:
Code:
Public Function fncLatestTable()
Dim sSQL As String
Dim sFile As String
Dim dFile As Date
Dim db As DAO.Database
Dim td As DAO.Tabledef

sSQL = "SELECT a.Name, a.ID, a.DateCreate " & _
"FROM MSysObjects AS a INNER JOIN (SELECT Max( DateCreate) as Latest " & _
"FROM MSysObjects " & _
"Where Flags = 0) AS b ON a.DateCreate = b.Latest;"

Set db = Currentdb
With db.Openrecordset(sSQL)
    sFile = .Fields(0).Value
    dFile = .Fields(1).Value
End With
Set td = db.TableDefs(sFile)
td.Name = sFile & "_" & Format(dFile, "mm\/dd\/yyyy")
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set td = Nothing
Set db = Nothing
End Function


Thanks brother, this works....
 

Rakesh935

Registered User.
Local time
Today, 17:01
Joined
Oct 14, 2012
Messages
71
All,

Many thanks for your suggestions...although the VBA code works perfectly, I was thinking if this could be achieved through SQL query.

It would be great if anyone could give me some idea in achieving the same through SQL query..

Thanks,
Rakesh
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:31
Joined
Feb 19, 2013
Messages
16,553
you can't do things like rename tables in sql

you can use a make table though
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:31
Joined
Feb 28, 2001
Messages
27,001
This is gonna be a tracking mechanism by dates. And since the nature of the data would be incremental therefore it would be ideal to use the latest table for further processing.

But if the dates are part of the table, you can save everything in one table and specify a date range in the WHERE clause. Renaming a table just to isolate a group of dates makes sense if and ONLY if the tables have different structures - but your comment sort of implies that they don't have different structures, just different content.

Your method is DECIDEDLY an overkill solution. If I'm wrong, you need to explain what you are doing a bit better because from what I see, you are using extreme measures of data isolation that are really not warranted if all you wanted to do was focus on a date range. You could do that from a single table by creating a SELECT query with a date-restrictive WHERE clause and reduce a LOT of data churning. Plus, having multiple tables prevents longer-term aggregate queries unless you use UNION queries (awkward and limiting) or duplicate your data (wasteful of space and totally unnormalized.)
 

Mark_

Longboard on the internet
Local time
Today, 04:31
Joined
Sep 12, 2017
Messages
2,111
This is gonna be a tracking mechanism by dates. And since the nature of the data would be incremental therefore it would be ideal to use the latest table for further processing.

Just to be clear, rather than using a date range you want to have your program constantly change which table it is referencing? You also want to make referential integrity very difficult (probably have to manually do that) and validation against previous dates incredibly difficult?

For myself, I'd make a query that limits to a date range rather than having multiple tables.

Where something like this does make some sense is if you need to do periodic backups to support fiscal reports. Then you are taking a snapshot of your data though, and only so an audit can validate what is reported.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 19, 2002
Messages
42,981
Just to be clear, the solution that Mark is suggesting and which I support, takes a variable. The date ranges are NOT hard coded. You have ONE query, it either prompts (bad option) or references form fields (best option) to get the start and end date of the range. Or if the range is always only ONE year or ONE month, use just the year or year and month.

The path you are going down requires constant maintenance and provides no benefit for all the extra work.
 

isladogs

MVP / VIP
Local time
Today, 11:31
Joined
Jan 14, 2017
Messages
18,186
Even for financial reports, I'm not convinced the OP's approach is worthwhile.
In the dim and distant past a snapshot report was used for this purpose.
These days, we use PDFs instead but the idea is still the same.
 

Mark_

Longboard on the internet
Local time
Today, 04:31
Joined
Sep 12, 2017
Messages
2,111
Even for financial reports, I'm not convinced the OP's approach is worthwhile.
In the dim and distant past a snapshot report was used for this purpose.
These days, we use PDFs instead but the idea is still the same.

Colin,

Not talking about a PDF report, more an actual copy of the data. That way if an auditor comes in to see how you got a specific value you can pull up all records as they were to support it. Avoids issues when you discover (afterwards) that data was put in wrong is is corrected.

Most likely NOT what the original poster is after though.
 

Users who are viewing this thread

Top Bottom