Brendan Reynolds Q: List all Tbls & Move Using SQL (Ado)

R

rebelscum0000

Tbls : Table
Fields:
Id
Table
Include

Hello Group, I need to Move the list of all tables in the Current Dbs
to another tlb
Called Tbls in the same Current Dbs My code is as follows:

Sub All_Tables_SMU_II()

'This Code list all the Tables in the Current Database

Dim aob As AccessObject
Dim Counter As Variant 'Counter *


'Initialize variables
Counter = 0

'CurrentData is the object that contains the tables

For Each aob In CurrentData.AllTables

Counter = Counter + 1

If Left$(aob.Name, 4) <> "MSys" Then
MyTableName = aob.Name
MsgBox MyName

Tables_AllTbls = aob.Name

MsgBox "Obj #" & Counter_AllTbls & " " & Tables_AllTbls

'SQLStatement
SQL2 = "INSERT INTO Tbls ? & _
" SELECT ? & _
" FROM ? " & _
" WHERE ?
" ORDER ?

DoCmd.RunSQL SQL2

End If
Next aob

End Sub

----> My Problems and Questions--->

1.- I need to change this code as DAO Recordest
2.- I need to update The Counter, MyTableName and ORDER from the
Counter in the SQLStatement
3.- I do not have any idea how will be the sintaxys of the SQLStatement
for this code

Thank you very much in advance

Regards
Antonio Macias
 
T

Tom Wickerath

Hi Antonio,

Will this work for you?


Option Compare Database
Option Explicit

Sub All_Tables_SMU_II()
On Error GoTo ProcError

Dim strSQL As String
strSQL = "INSERT INTO Tbls ([TableName]) " _
& "SELECT msysobjects.Name AS TableName " _
& "FROM msysobjects " _
& "WHERE msysobjects.Type In (1,6) " _
& "AND Left([Name],4) Not In ('msys','Tbls') " _
& "ORDER BY msysobjects.Name;"

CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure All_Tables_SMU_II..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
R

rebelscum0000

Hi Tom Wickerath

Yes Thank you very much it works, I did not have any idea how to deal
with
this SQLStatement.
Now the problem is there are dups in my Tbls "Table" 3 for each Table
that I have in
My CurrentDB, Do you know why the code is doing this?

Id Table
0 Default_Tlb
0 Default_Tlb
0 Default_Tlb
0 Default_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Symantec_Tlb
0 Symantec_Tlb
0 Symantec_Tlb
0 Symantec_Tlb

Also I do not understand the wHERE statement ("WHERE msysobjects.Type
In (1,6) " )
Type (1,6) Could you please explain what does that mean, I aks you this
because I do
not want only cut and paste into my code, I would like to understand
what I am
coding in order to understand it and maybe help others

My Code changes a llttle Now i think is ADO and is version SMU_IV :)

Thanks in advance

Regards,
Antonio Macias


Sub All_Tables_SMU_IV()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim lngRc As Long
Dim strSQL As String

On Error GoTo ProcError


Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then

MyName = tdf.Name
MsgBox MyName

'SQLStatement
strSQL = _
"INSERT INTO Tbls (
) " & _
"SELECT msysobjects.Name AS TableName " & _
"FROM msysobjects " & _
"WHERE msysobjects.Type In (1,6) " & _
"AND Left([Name],4) Not In ('msys','Tbls') " & _
"ORDER BY msysobjects.Name;"


CurrentDb.Execute strSQL, dbFailOnError

End If
Next tdf
Set db = Nothing

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure All_Tables_SMU_II..."
Resume ExitProc
End Sub
 
T

Tom Wickerath

Hi Antonio,

First, I forgot to mention that I substituted "TableName" as the name of one
of the fields, in place of "Table". The reason I did this is that table is
considered a reserved word. You should avoid the use of any reserved words
for things that you assign a name to in Access. Here is a very good compiled
list of reserved words:

http://www.allenbrowne.com/AppIssueBadWord.html

The code does not protect against duplicates. Each time you run it, you will
add another set of duplicates. You can modify the procedure by clearing the
table first:

Currentdb.Execute "DELETE * FROM Tbls"
Also I do not understand the wHERE statement ("WHERE msysobjects.Type
In (1,6) " )
Click to expand...

We are just querying the MsysObjects table for a list of Object Names. This
table includes a field named Type. It just so happens that Type=1 corresponds
to local tables, and Type=6 corresponds to linked tables. To get an idea of
how this works, create a new query without selecting any tables. Change to
SQL View. Enter the following SQL statement and run the query:

SELECT * FROM msysObjects

I would like to understand what I am
coding in order to understand it and maybe help others
Click to expand...

Excellent. I applaude your attitude!

My Code changes a llttle Now i think is ADO and is version SMU_IV :)
Click to expand...

Nope. The revised procedure you showed is still very much DAO code. I'm not
sure why you are still iterating the tabledef collection. This should not be
necessary to achieve your objective.


Try this revision to prevent the duplicates:

Option Compare Database
Option Explicit

Sub All_Tables_SMU_II()
On Error GoTo ProcError

Dim strSQL As String

strSQL = "DELETE * FROM Tbls"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO Tbls ([TableName]) " _
& "SELECT msysobjects.Name AS TableName " _
& "FROM msysobjects " _
& "WHERE msysobjects.Type In (1,6) " _
& "AND Left([Name],4) Not In ('msys','Tbls') " _
& "ORDER BY msysobjects.Name;"

CurrentDb.Execute strSQL, dbFailOnError

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure All_Tables_SMU_II..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

rebelscum0000 said:
Hi Tom Wickerath

Yes Thank you very much it works, I did not have any idea how to deal
with this SQLStatement. Now the problem is there are dups in my Tbls
"Table" 3 for each Table that I have in My CurrentDB, Do you know why
the code is doing this?

Id Table
0 Default_Tlb
0 Default_Tlb
0 Default_Tlb
0 Default_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Nero_Tlb
0 Symantec_Tlb
0 Symantec_Tlb
0 Symantec_Tlb
0 Symantec_Tlb

Also I do not understand the wHERE statement ("WHERE msysobjects.Type
In (1,6) " )
Type (1,6) Could you please explain what does that mean, I aks you this
because I do
not want only cut and paste into my code, I would like to understand
what I am
coding in order to understand it and maybe help others

My Code changes a llttle Now i think is ADO and is version SMU_IV :)

Thanks in advance

Regards,
Antonio Macias


Sub All_Tables_SMU_IV()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim lngRc As Long
Dim strSQL As String

On Error GoTo ProcError


Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then

MyName = tdf.Name
MsgBox MyName

'SQLStatement
strSQL = _
"INSERT INTO Tbls (
) " & _
"SELECT msysobjects.Name AS TableName " & _
"FROM msysobjects " & _
"WHERE msysobjects.Type In (1,6) " & _
"AND Left([Name],4) Not In ('msys','Tbls') " & _
"ORDER BY msysobjects.Name;"


CurrentDb.Execute strSQL, dbFailOnError

End If
Next tdf
Set db = Nothing

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure All_Tables_SMU_II..."
Resume ExitProc
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to list Objects in DB lists deleted SQL 4
Sort SQL 22

Top