Rename a table in an Ms. Access Database from VB ? 1

[MichaelRed]

For obscure (and unworthy) reasons, I want/need to change the name of one of the tables in an Ms. Access data base, depending on a specific process.

I have two different tables in the database One of them is a linked Pervasive/BTRieve file which is used in conjunction w/ a companion program which I have NO influence on (much less control of). The other is a native Ms. Access table which is a quasi clone of the BTRieve table (discarding elements which My Program doesn't ever use). It (the Ms. Access quasi clone) is also lined to the main Ms. Access database.

When running with the companion program, I need to "ReName" the BTRieve table as "SKU". When running in standalone mode, I need to Rename the Ms. Access table as "SKU".

Using info from the MSysObjects (hidden) table, I can easily see which file is currently named "SKU", and re-set it's name to some appropiate value, and then rename the desired one th "SKU".

Unfortunatley, this is all done in an Ms. Access module, using the DoCmd.Rename ... function. The 'real' application is written in VB6, so use of the Ms. Access module is not quite 'legal' as far as I know.

If anyone knows how to accomplish this functionally from within VB, I would appreciate the feedback.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hi,
I haven't used that DoCmd.Rename method before, however, if it works in Access you can use it in VB as follows:

Dim appAccess As Access.Application

Set appAccess = New Access.Application

appAccess.OpenCurrentDatabase "C:\Temp\MyDatabase.mdb"

'Do some example misc. stuff with appAccess...
appAccess.DoCmd.DeleteObject, acTable, "tblMyTable"

appAccess.CloseCurrentDatabase

You must add the referrence to your project for "Microsoft Access X.0 Object Library", where "X" is "8" or "9" depending on your version of Access.
Rob Marriott
rob@career-connections.net

 

[MichaelRed]

Yes, I had thought of this, however this requires the 'presence' of Ms. Access on the client's machine/system and requires that I be able to verify that he has it, where it is and what version it is. Also needs the resources to actually run the thing. My 'Goal' was to use Ms. Access ONLY as a data provider and not have ANY Code/queries ... in the db. That way I need only VB.

In pondering the problem, I thought of just setting a flag for the two modes of operation, and setting the recordset reference to the "desired" value whenever the table is referenced/instaantiated, Like:

Dim RefSku as String
Dim rstSku as Recordset

If (MyFlag) Then
[tab]RefSkuy = "tbl_Sku_MDB"
Else
[tab]RefSkuy = "tbl_Sku_BTR"
End If

Set rstSKU = dbs.openrecordset(RefSku, dbopendynaset)


Thereafter, any reference to rstSku will get the appropiate data.

I haven't quite convinced myself that it will work seamlessly with queries, but I'm sure to find out (somehow).


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ciaranr]

Microsoft provides a subset of ADO called ADOX that will do all this for you. Search the Microsoft site (can't remember now where it is!) and you will find a volumous page of descriptions and examples. Easy to use and worth the time learning.

This will then enable you, of course, to update a user's database as you provide software updates: new fields, redefined fields, tables etc


have fun
ciaran

ciaran
ciaranr@albany.jrc.net.au

 

[MichaelRed]

ciaranr,

Tried RDO/ADO. They impose WAY to much overhead in time to be useful in a realtime (transaction) orientated network situation. I will attempt to see if the ADOX implementation requires less overhead. I need to keep the average transaction time in the order of 3 seconds, with up to 40 concurrent users. So for, I can only do this wit direct API calls for the BTRieve table and the remainder of the tables in native Ms. Access. RDO makes average transaction time (for the test set) run up to approx 4.5 sec. ADO does worse, @ almost six sec. Using just the renaming (set up seperatly with Ms. Access installed), transaction time averages just over two seconds. I had thought that RDO/ADO only increased connection time, but it appears to DRAMATICALLY increase execution time. Perhaps if I increase the number of connections so their contexts are not changed during execution the impact would be less, however I believe this requires significantly more memory for execution, unless swapping is allowed - which, or course, would again cause a slowdown.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[oneshadow]

Hi MichaelRed,

I made a table named 'Cust' and successfully renamed it to 'Supp' via VB code.
I'm using Access 2000 and DAO 3.6

Here's my code :

Dim Maindb As DAO.Database
Dim iLoop as Integer

'Open database
Set Maindb = DBEngine.OpenDatabase("C:\My Temp\Dummy.mdb&quot

'looping to find the table you wish to rename
For iLoop = 0 To Maindb.TableDefs.Count - 1
If Trim(UCase(Maindb.TableDefs(iLoop).Name)) = "CUST" Then
'change the name
Maindb.TableDefs(iLoop).Name = "SUPP"
Exit For
End If
Next

Set Maindb = Nothing


Or you can access through the table name directly :

Maindb.TableDefs("CUST&quot.Name = "SUPP"

It will rename the table without loop for every table in database.

Hope this helps.

 

[MichaelRed]

oneshadow,

Thanks. This "Looks Like" the real thing. Hopefully, it will work w/ Ms. Access '97 and not require DAO, although evein if it does, it would APPEAR that I can get away w/ just the localized instance of DAO for the renaming process.

This deserves a Star, however I can't vote on this thread, as I asked the question.

Thanks again.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over