DAO.TableDefs Collection question.

[DaveO]

Guys, I have built this code so far...

Dim dbMe As DAO.Database
Dim dbBmthDb As DAO.Database

Dim wrkNewSpace As Workspace
Dim conConnect As Connection

Dim tblRemote As DAO.TableDef
Dim tblDbRemote As DAO.TableDef

Set dbMe = CurrentDb
Set wrkNewSpace = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set dbBmthDb = wrkNewSpace.OpenDatabase("Application", , ,
"ODBC;DATABASE=Application;DSN=Stack1 Remote Tables;UID=****;PWD=****")

For Each tblRemote In dbBmthDb.TableDefs

MsgBox tblRemote.Name


Next tblRemote


Set dbMe = Nothing
Set wrkNewSpace = Nothing
Set dbBmthDb = Nothing

Now it's erroring when it gets to the For Each statement, saying that 'Error
3251 Operation is not supported for this type of object'.

I'm trying to reference the .tabledefs collection so not sure why this would
cuase me problems.

Any help would be great.

Thanks.

 

[David Lloyd]

Dave:

I tested your code using a remote Access database through a DSN. You did
not specify the type of database your DSN represents, so I had to make an
assumption. In the CreateWorkspace method, you specified "dbUseODBC" which
creates an ODBCDirect workspace. When I changed this parameter to
"dbUseJet" I was able to run the code. For example:

Set wrkNewSpace = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseJet)

If this does not solve your issue, please provide more information regarding
your remote database (type, etc.).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Guys, I have built this code so far...

Dim dbMe As DAO.Database
Dim dbBmthDb As DAO.Database

Dim wrkNewSpace As Workspace
Dim conConnect As Connection

Dim tblRemote As DAO.TableDef
Dim tblDbRemote As DAO.TableDef

Set dbMe = CurrentDb
Set wrkNewSpace = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set dbBmthDb = wrkNewSpace.OpenDatabase("Application", , ,
"ODBC;DATABASE=Application;DSN=Stack1 Remote Tables;UID=****;PWD=****")

For Each tblRemote In dbBmthDb.TableDefs

MsgBox tblRemote.Name


Next tblRemote


Set dbMe = Nothing
Set wrkNewSpace = Nothing
Set dbBmthDb = Nothing

Now it's erroring when it gets to the For Each statement, saying that 'Error
3251 Operation is not supported for this type of object'.

I'm trying to reference the .tabledefs collection so not sure why this would
cuase me problems.

Any help would be great.

Thanks.

 

[DaveO]

The database I'm trying to hit is a SQL server db.

By altering the dbUseODBC to dbUseJet it throws an error saying '3024. Could
not find file 'Application'.

Now I've fiddled around and it's the name it can't find and nothing wrong
with the connection string. When you do an .OpenDatabase the first parameter
is the name. If it's a SQL server how would I reference it?

Thanks for the help so far!!!

 

[David Lloyd]

Dave:

The problem you run into is that DAO supports a different object model for
JET versus ODBCDirect. For example, ODBCDirect supports a QueryDefs
collection, but not a TableDefs collection, which is why you encountered
your original error. In general, ODBCDirect offers a more limited object
model for DAO than does JET. If you use the Help in the Access Visual Basic
Editor and search on "DAO", there are some very nice object model diagrams
that detail the DAO object models for both JET and ODBCDirect.

That being said, it you are trying to enumerate the tables in an SQL Server
database, you could alternatively use the OpenSchema method of the ADO
connection object. For example:

Function GetTables()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.ConnectionString = "ODBC;DATABASE=Application;DSN=Stack1 Remote
Tables;UID=****;PWD=****"
cn.Open
Set rs = cn.OpenSchema(adSchemaTables)

Do Until rs.EOF
MsgBox rs("Table_Name")
rs.MoveNext
Loop

Set cn = Nothing
Set rs = Nothing

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


The database I'm trying to hit is a SQL server db.

By altering the dbUseODBC to dbUseJet it throws an error saying '3024. Could
not find file 'Application'.

Now I've fiddled around and it's the name it can't find and nothing wrong
with the connection string. When you do an .OpenDatabase the first parameter
is the name. If it's a SQL server how would I reference it?

Thanks for the help so far!!!

 

[DaveO]

After scouring the Help files I managed to cobble this together and
thankfully it works!!!

Dim dbMe As DAO.Database
Dim dbBmthDb As DAO.Database

Dim wrkNewSpace As Workspace
Dim conConnect As Connection

Dim tblRemote As DAO.TableDef
Dim tblDbRemote As DAO.TableDef

Dim strTableName As String
Dim strSourceTableName As String
Dim strConnect As String

Set dbMe = CurrentDb
Set wrkNewSpace = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseJet)
Set dbBmthDb = wrkNewSpace.OpenDatabase("Stack1 Remote Tables",
dbDriverNoPrompt, False, "ODBC;DATABASE=Application;DSN=Stack1 Remote
Tables;UID=****;PWD=****")
strConnect = "ODBC;DATABASE=Application;DSN=Stack1 Remote
Tables;UID=obreporting;PWD=obreporting"

For Each tblRemote In dbBmthDb.TableDefs

strSourceTableName = tblRemote.Name
strTableName = Left(strSourceTableName, 3) & "_" &
Mid(strSourceTableName, 5, Len(strSourceTableName) - 4)

If Right(strTableName, 4) = "0805" Then

Set tblDbRemote = dbMe.CreateTableDef(strTableName)

With tblDbRemote
.Connect = strConnect
.SourceTableName = strSourceTableName
End With

dbMe.TableDefs.Append tblDbRemote

End If

Next tblRemote


Set dbMe = Nothing
Set wrkNewSpace = Nothing
Set dbBmthDb = Nothing

Thanks very much for the help David. One last question...

How advanced in skill set would a person need to be able to do this in
Access? Trying to guage my skill level and DAO is an area I'm using more and
more and would like to know where this would peg me.

Thanks for the help, you're a star!