ALTER TABLE SQL in MS Access

[nidgep]

Hi

Can anyone tell me of the correct SQL statement to rename a table within MS Access the through SQL (or T-SQL) - programmatically.

For example:-
ALTER TABLE TABLE_1 RENAME TABLE_2

In SQL Server this would be done by using the sp_Rename stored procedure as follows:-
EXEC sp_rename 'Table_1', 'Table_2'

Can anyone help?

Thanks in advance

 

[dodge20]

You can't use ALTER TABLE as that's used for changing the table-definition (add fields etc.)

The only way for SQL I see is to issue a make-table query followed by a DROP of the old table.

It''s easier done by VBA...

Just:

function fncAlterTable(strOldName as string, strNewName as string)

currentdb.tabledef("strOldName&quot.name = strNewName


Dodge20

 

[nidgep]

HI Dodge20

Thanks for replying.

I can already do this in Visual Basic using an Access.Application object. but I want to move away from using the intrinsic Access objects because the server that the code will reside on does not have MS Access installed.

The function I am currently using is as follows:-


Public Function DeleteAndRenameTable(ByVal thePath As String, ByVal theDB As String, _
ByVal CurrentTable As String, ByRef strErr As String) As Integer

On Error GoTo DeleteAndRenameTable_Err

Dim objAcc As Access.Application
Set objAcc = New Access.Application
objAcc.OpenCurrentDatabase thePath & theDB

'we now have a temp table and a current table
'so delete the current and rename the temp to replace it

On Error Resume Next
objAcc.DoCmd.DeleteObject acTable, CurrentTable

On Error GoTo DeleteAndRenameTable_Err
objAcc.DoCmd.Rename CurrentTable, acTable, CurrentTable & "Temp"

DeleteAndRenameTable = 0 'good return code

DeleteAndRenameTable_Exit:
If Not objAcc Is Nothing Then
objAcc.CloseCurrentDatabase
Set objAcc = Nothing
End If
Exit Function

DeleteAndRenameTable_Err:

DeleteAndRenameTable = 1 'bad return code
strErr = Err.Number & " [" & Err.Description & "]"
Resume DeleteAndRenameTable_Exit

End Function



As you can see I have already used the DoCmd.Rename function to fulfill this purpose.

Any more suggestions...?

 

[dodge20]

I think you are out of luck here. In true Transact Sql you could use the sp_rename, but this isn't supported by Access. The only way I can think of is to create a new table and drop the old one.

Dodge20

 

[nidgep]

Thanks Dodge20

I think that I will create a function within the database to do the renaming action.
This function will be 'called' from within a query which receives the 'name' parameters and passes them on to the function.
This way, MS Access does not have to reside on the server.

One final query, can the rename function be called directly from outside the database instead, or is it for explicit use internally?

Thanks for your help so far.

 

[dodge20]

If you have VB, you can create a small VB executable that will connect to
your db thru either ADO for pure SQL statements or DAO to perform native Jet
functions. Just set the Project/References to the proper .DLL libraries, and
point the .connection to your database

Here is an example I found, this isn't my code.

The attached VB code links to any Access database and executes a public
Module function which reloads a bunch of tables, then compacts the database
and externally backs it up into a timestamped archive.

Basically all you need is the shell. Modify it to call your own function,
compile it and call. In this case, the db name is passed in on the command
line.



Option Explicit

Dim appAccess As Access.Application
Dim DBOldPath As String
Dim DBNewPath As String

Sub Main()
Dim WkTime As Date

On Error GoTo DPUAutoLoadError

If Trim$(Command$) = "" Then
Exit Sub
Else

DBOldPath = Mid$(Command$, 2, Len(Command$) - 2)

Set appAccess = CreateObject("Access.Application.8&quot

appAccess.OpenCurrentDatabase DBOldPath, False
appAccess.Run "DPUAutoUpdate"
appAccess.CloseCurrentDatabase

WkTime = Now()
DBNewPath = Mid$(DBOldPath, 1, InStr(1, DBOldPath, ".", 1) - 1) & _
"_" & Format$(DatePart("yyyy", WkTime), "0000&quot & "_" & _
Format$(DatePart("m", WkTime), "00&quot & _
Format$(DatePart("d", WkTime), "00&quot & "_" & _
Format$(DatePart("h", WkTime), "00&quot & _
Format$(DatePart("n", WkTime), "00&quot & _
Format$(DatePart("s", WkTime), "00&quot & _
".mdb"

appAccess.DBEngine.CompactDatabase DBOldPath, DBNewPath
Kill (DBOldPath)
appAccess.DBEngine.CompactDatabase DBNewPath, DBOldPath
appAccess.Quit 1
Set appAccess = Nothing
End If
Exit Sub

DPUAutoLoadError:
MsgBox "Error " & Err & ": " & Err.Description
Resume Next
End Sub


Dodge20

 

[nidgep]

Thanks again dodge20

I think that I have taken up enough of your time now.

I will use some of the ideas that you have presented to work out a compromise. Maybe installing MS Access 2000 runtime on the server would be easiest, so that I can get 'access' to the required objects and exe's.

Thanks again

nidgep

 

[RobCPA]

You can use the following DAO code to rename a table. I tested it using Excel VBA on a machine that doesn't have Access installed and it worked.


Function RenameAccessTable()
dim ws as workspace
dim db as database
dim tdf as tabledef

set ws = createworkspace("newodbcworkspace", "admin", "",dbusejet)

set db = ws.opendatabase("p:\test.mdb&quot
set tdf = db.tabledefs("table1&quot

tdf.name "NewTableName"

End Function


Rob