Translate VBA code to VBscript

[sebes]

Hello all,

I need to change the name of a table in a Access 2007 database. The code below(VBA) does what I need BUT I need it in VBScript. Can someone help me "translate" it ?

Sub changetablename()
Set db = CurrentDb()
For Each n In db.tabledefs
tablename = n.Name
If tablename = "_help pages" Then
n.Name = "helppages"
End If
Next
Set db = Nothing
End Sub

In VBscript I managed to go as far as connecting to the database as follows(do while below only to test that connection works - and it works):

Set conn = CreateObject("ADODB.Connection")
strConnect = "DSN=Mdb;DBQ=c:\umana\data\project.MDB;DefaultDir=c:\umana\data;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
conn.Open strConnect
StrSQL = "Select * from helppages"
Set rs = conn.Execute(StrSQL)
Do While not rs.EOF
msgbox(rs(1))
rs.MoveNext
Loop

I'm running the VBScripts from a Visual Foxpro program, that's also working. So all I need is to continue this VBscript to accomplish what I've done in VBA.

Thanks in advance.

-gl

 

[Geates]

copy the code into a .vbs file and double click it (generally).

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[sebes]

Thanks Geates,

I don't think that would help since I need to call this from a VFP program.

In the second piece of code(VBScript) , after I get connected to the database I have to "integrate" the VBA code that is working.

Just don't know how...because I'm a beginner.

-gl

 

[PWise]

try

Sub changetablename()
dim appaccess
dim db
set appaccess = createobject(access.applaction)
Set db = appaccess .opencurrentdb(pathtofile)
For Each n In db.tabledefs
   tablename = n.Name
   If tablename = "_help pages" Then
       n.Name = "helppages"
   End If
Next
Set db = Nothing
End Sub

 

[sebes]

Tried that but no luck. The (adapted)code below gives an error message:
Object doesn't support this property or method: 'opencurrentdb'

dim appaccess
dim db
set appaccess = createobject("access.application")
Set db = appaccess.opencurrentdb("c:\umana\data\project.mdb")
For Each n In db.tabledefs
tablename = n.Name
msgbox(tablename)
If tablename = "helppages" Then
n.Name = "helpsssspages"
End If
Next
Set db = Nothing

-gl

 

[sebes]

The code below will open the database and change the table name.

dim AppAccess
dim Db

Set AppAccess = CreateObject("Access.Application")
AppAccess.OpenCurrentDatabase "c:\umana\data\project.mdb"
Set Db = AppAccess.CurrentDb()

For Each n In Db.TableDefs
tablename = n.Name
If tablename = "helppages4" Then
n.Name = "helppages5"
End If
Next

AppAccess.CloseCurrentDatabase
Set db = Nothing
Set AppAccess = Nothing

This is exactly what I need. Very useful when tables have blanks or funny characters in the name which are not accepted by other platforms.

Thanks for your help.

-gl