newbie question on TableDefs

I am swtiching from access/Jet to msde, and cannot figure out how to
retrieve the tabledefs from msde

This is how I did it with Jet:

Dim T As TableDef, D As Database, dbpath As String
dbpath = Application.CurrentProject.Path & "\data.mdb"
Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath)
Set T = D.TableDefs(symbolName)

What is the equiavlent for msde/mssql?

MANY Thanks in adv."Ernesto" <tsh@.mathematicuslabs.com> wrote in message
news:0vWdnQohvoduenfdRVn-uA@.speakeasy.net...
> I am swtiching from access/Jet to msde, and cannot figure out how to
> retrieve the tabledefs from msde
> This is how I did it with Jet:
> Dim T As TableDef, D As Database, dbpath As String
> dbpath = Application.CurrentProject.Path & "\data.mdb"
> Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath)
> Set T = D.TableDefs(symbolName)
> What is the equiavlent for msde/mssql?
> MANY Thanks in adv.

I'm not sure exactly what information you need, but one or both of these may
be what you're looking for:

-- List of table names in the current database
exec sp_tables @.table_type = '''table'''

-- Structure of an individual table
exec sp_help MyTable

If that's not useful, then you might want to give some more details -
personally, I don't know what a tabledef is in Access. Also, check out the
"System Stored Procedures" and "Metadata Functions" topics in Books Online,
as there are many ways to retrieve different items of metadata. Finally, if
you're interested in using a COM interface, then SQLDMO can retrieve and
manipulate objects in MSSQL.

Simon|||Simon,
Thanks very much for your reply.
TableDefs are simply the design of the tables.
So it seems that sp_Help does this, as you had guessed I needed.

I looked this up in a few places but stilll it is not clear how I should use
it.
e.g.
http://msdn.microsoft.com/library/d...p_help_304w.asp

says that it returns a "result set", but what is a "result set"? same as a
record set?
This shows how uninformed I am. But I have not found the syntax or how to
look at this result set.

Furthermore, the site says that sp_help works only on the current DB. How
can I specify the relevant database?

More of the code I have to work with is:

dbpath = Application.CurrentProject.Path & "\HOG.data.mdb"
'specify the db
Set D = DBEngine.Workspaces(0).OpenDatabase(dbpath) 'set the
db
Set T = D.TableDefs(symbolName) 'get the definition os
the table named symbolName

For i = 0 To T.Fields.Count - 1 ' look thriogh its fields to
see if "tdate" is one of them
If UCase(T.Fields(i).Name) = "TDATE" Then
isTimeSeries = True
...

If you have any further hints, I will be obliged.

E|||Ernesto (tsh@.mathematicuslabs.com) writes:
> says that it returns a "result set", but what is a "result set"? same as a
> record set?

Yes, "result set" is the proper terminology when you are talking databases.
(Just like "records" are called "rows" and "fields" are called "columns".

> This shows how uninformed I am. But I have not found the syntax or how to
> look at this result set.

You just say:

exec sp_help tbl

The "exec" keyword is optional if this is all you say, but if you are
looking at two, you need it at least for the second call to sp_help.

You run this command from Query Analyzer.

> Furthermore, the site says that sp_help works only on the current DB. How
> can I specify the relevant database?

You can say:

exec some_other_db..sp_help tbl

This applies to all system procedures (those that start with sp_).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You run this command from Query Analyzer.
> You can say:
> exec some_other_db..sp_help tbl
Thanks VERY much for following up.

One more thing, With apologies in advance if this is a stupid question:
I was asking about how to do it from VBA, instead of the Query analyzer.
Where/how will the result set be stored so that the code can inspect it.

TX|||Ernesto (tsh@.mathematicuslabs.com) writes:
> One more thing, With apologies in advance if this is a stupid question:
> I was asking about how to do it from VBA, instead of the Query analyzer.
> Where/how will the result set be stored so that the code can inspect it.

I would guess that you could use ADO to access the database and execute
the commands and get them in record sets.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks,
I found out that this is a direct substitute:

Set Rst = New ADODB.Recordset
Rst.Open "SELECT top 1 * FROM " & symbolName, ConnString

For I = 0 To Rst.Fields.Count - 1
If UCase(Rst.Fields(I).Name) = "TDATE" Then
isTimeSeries = True
...