get tables name from the Access database using vbscript!! 3

[qajussi]

Hi!

How do I get the table names from the access database using the vbscript and ado??

Can you help??
Thank you.

 

[RhythmAddict112]

That's easy, you just use google.

http://www.freevbcode.com/ShowCode.asp?ID=139

All hail the INTERWEB!

 

[snotmare]

Hello qajussi!

Try something like this...

Dim DB
Dim table
	
objAccess.OpenCurrentDatabase "C:\testdb.mdb"
Set db = objAccess.CurrentDB
	
For Each table In db.TableDefs
    MsgBox table.Name
Next
	
objAccess.Quit True
Set DB = Nothing

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[qajussi]

Thank you RhythmAddict112!!!

 

[RhythmAddict112]

Glad to help, enjoy!

All hail the INTERWEB!

 

[qajussi]

snotmare!!

This is even better.
Shorter code.

I found a way to do it with adox.catalog.

You are using the schema object or something??
I like your code better.
Thanks a lot.

 

[snotmare]

Oh crap, I forgot the most important code, my bad!!!

Dim objAccess
Dim DB
Dim table

[red]Set objAccess = CreateObject("Access.Application")[/red]

objAccess.OpenCurrentDatabase "C:\testdb.mdb"
Set db = objAccess.CurrentDB
    
For Each table In db.TableDefs
    MsgBox table.Name
Next

Set DB = Nothing    
objAccess.Quit True
[red]Set objAccess = Nothing[/red]

My appologies. You can use this method to control almost any Microsoft application!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[qajussi]

snotmare!

It works fine.

It also gives me the system tables.
Do you have any idea to skip these tables??

Another thing happened was it waits for me click OK button to open the database.
This never happened to me before.
Why doesn't access the database without bringing me the popup to click to open the database.

Thanks.

 

[RhythmAddict112]

fyi, the code i provided skips system tables and has error handling

All hail the INTERWEB!

 

[PHV]

For Each table In db.TableDefs
If UCase(Left(table.Name, 4)) <> "MSYS" And Left(table.Name, 1) <> "~" Then
MsgBox table.Name
End If
Next

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

 

[qajussi]

RhythmAddict112!

Yes, your code works fine.
I am looking for ADO code though.
Thank you very much for your help.

 

[snotmare]

I've never had to click an ok button either, that's odd. The only things I can think of, is it's 1 - something in your code that is executed every time the database is open (but you just said that wasn't the case), 2 - you have security set up for your database that conflicts with the way the script opens it up, or 3 - there is a phantom setting somewhere that I've never heard of that is causing you this grief.

Sorry not much help, I've never ran into that before. Since I don't have your database, I don't know how to recreate the problem.

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[qajussi]

snotmare,

I change the macro's security setting from the access tool menu to the LOW.
And the vbscript didn't open the database and ask if i want to open it.

Anyway, I am tring update all the tables with its table names.
Each table name is country name.
Each table has a field called country.
I want to update each table's country column with corresponding country name.

If it is Albania table, i want to insert the country column with the "Albania".
I want to update all the tables.

I tried but i am not doing well.
Can you help??


Dim cbjCon2, objCat, objConn

Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=ARG.mdb;"
objConn.Open

Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = objConn

For Each tbl In objCat.Tables

If UCase(Left(tbl.Name, 4)) <> "MSYS" And Left(tbl.Name, 1) <> "~" Then
tableName =tbl.Name
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=2ARG.mdb;"
conn.Open
Set objRs = CreateOBject("ADODB.RecordSet")
'MsgBox table.Name

objRs.Open tableName, conn, 3,3
objRs.AddNew
objRs("Country") = tableName
objRs.Update
End If
Next


I get an error saying i can't open two object connection or something.

 

[PHV]

You may try this:
For Each tbl In objCat.Tables
If UCase(Left(tbl.Name, 4)) <> "MSYS" And Left(tbl.Name, 1) <> "~" Then
tableName =tbl.Name
Set objRs = CreateOBject("ADODB.RecordSet")
'MsgBox table.Name
objRs.Open tableName, objConn, 3,3
objRs.AddNew
objRs("Country") = tableName
objRs.Update
objRs.Close
Set objRs = Nothing
End If
Next

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

 

[snotmare]

There lots of ways to do this. If you have trouble with what PHV provided, here is another option...

Dim objAccess
Dim DB
Dim objTable
Dim objRS
Dim strSQL

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase "C:\testdb.mdb"
Set db = objAccess.CurrentDB
    
For Each objTable In db.TableDefs
    strSQL = "SELECT * FROM " & objTable.Name
    Set objRS = DB.OpenRecordset(strSQL) 
    objRS.AddNew
    objRS("Country") = objTable.Name
    objRS.Update
    objRS.Close
    Set objRS = Nothing
Next

Set DB = Nothing    
objAccess.Quit True
Set objAccess = Nothing

Hey, if you ever get that prompt thing figured out, you'll have to let us know!

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[qajussi]

Thank you PHV & snotmare!!

I think I am doing this wrong.
The code above is for adding a new record, isn't it??
I am tring to insert "country name" to the Country column which are all empty.

Dim objAccess
Dim DB
Dim objTable
Dim objRS
Dim strSQL

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase "C:\project\StrongHaven\Deliverable\ARG\ARG_Update.mdb"
Set db = objAccess.CurrentDB

For Each objTable In db.TableDefs
strSQL = "SELECT * FROM " & "[" & objTable.Name & "]"
Set objRS = DB.OpenRecordset(strSQL)
DO UNTIL objRS.EOF
'objRS("Country") = objTable.Name
'objRS.Update "Country", objTable.Name
objRS("Country").Value = objTable.Name
objRS.Update
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
Next

Set DB = Nothing
objAccess.Quit True
Set objAccess = Nothing

MsgBox "Done"

I get an error saying I need to use addNew or edit??

Thank you for your help and time.

 

[PHV]

Do Until objRS.EOF
objRS.Edit
objRS("Country").Value = objTable.Name
objRS.Update
objRS.MoveNext
Loop

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

 

[qajussi]

Thanks Million as always PHV!
Finally it works..I am sorry I didn't ask correct questions.
But I have to tell you I learned more about the ADO.

Thanks a lot PHV & snotmare!!
You guys are awesome!!