Query to get Table schema

Thanks for taking time to read my questions.

I'm looking for a way in Access that I could run a query that would
return table schema information. Specifically I need to get the
column name, data type and size of the column. In SQL there is a
stored procedure that does this 'SP_Help <table Name>. This command
gives me more than I need but I hope the example will help.

I'm writing an ASP search page. I'm hoping to send the page a
parameter (table Name) and it will dynamically build the search page
from the schema data. I have this working with a modified Stored
Procedure in MS SQL.

Thanks Again!

NG

re: Query to get Table schema

"N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=blue]
> Thanks for taking time to read my questions.
>
> I'm looking for a way in Access that I could run a query that would
> return table schema information. Specifically I need to get the
> column name, data type and size of the column. In SQL there is a
> stored procedure that does this 'SP_Help <table Name>. This command
> gives me more than I need but I hope the example will help.[/color]

You could write a procedure in VBA and DAO to emulate the SQL
Server SP_HELP procedure. Here's something to start with:

Public Function sp_help() As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String

Set db = CurrentDb()
For Each tbl In db.TableDefs
Debug.Print ""
Debug.Print tbl.Name
For Each fld In tbl.Fields
Debug.Print " " & fld.Name & " " & fld.Type
Next
Next
Set db = Nothing
End Function


Have fun!

-Mark

re: Query to get Table schema

Mark thank you for you response and your quick posting.

I'm not very familiar with DAO I using ADO to access my database. I
will continue to research you suggestion but to you have an ADO
example? I could not find and information on the CurrentDB()


This typically the way I access the DB.

sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"

Set objDB = Server.CreateObject("ADODB.Connection")
objDB.Open sDBName

SQLQuery = "select * from sayings"
objDB.execute(SQLQuery)

If you have anything else to help I'm very much appreciative

On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <nospam@thanksanyway.org>
wrote:[color=blue]
>
>"N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=green]
>> Thanks for taking time to read my questions.
>>
>> I'm looking for a way in Access that I could run a query that would
>> return table schema information. Specifically I need to get the
>> column name, data type and size of the column. In SQL there is a
>> stored procedure that does this 'SP_Help <table Name>. This command
>> gives me more than I need but I hope the example will help.[/color]
>
>You could write a procedure in VBA and DAO to emulate the SQL
>Server SP_HELP procedure. Here's something to start with:
>
>Public Function sp_help() As Boolean
> sp_help = True
> Dim db As DAO.Database
> Dim tbl As DAO.TableDef
> Dim fld As DAO.Field
> Dim sStmt As String
>
> Set db = CurrentDb()
> For Each tbl In db.TableDefs
> Debug.Print ""
> Debug.Print tbl.Name
> For Each fld In tbl.Fields
> Debug.Print " " & fld.Name & " " & fld.Type
> Next
> Next
> Set db = Nothing
>End Function
>
>
>Have fun!
>
>-Mark
>[/color]

re: Query to get Table schema

N. Graves wrote in message <0sm512domogklommsor8m28f1r5qilbih9@4ax.com>
:[color=blue]
> Mark thank you for you response and your quick posting.
>
> I'm not very familiar with DAO I using ADO to access my database. I
> will continue to research you suggestion but to you have an ADO
> example? I could not find and information on the CurrentDB()
>
>
> This typically the way I access the DB.
>
> sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
> Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"
>
> Set objDB = Server.CreateObject("ADODB.Connection")
> objDB.Open sDBName
>
> SQLQuery = "select * from sayings"
> objDB.execute(SQLQuery)
>
> If you have anything else to help I'm very much appreciative
>
> On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <nospam@thanksanyway.org>
> wrote:[color=green]
>>
>> "N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=darkred]
>>> Thanks for taking time to read my questions.
>>>
>>> I'm looking for a way in Access that I could run a query that would
>>> return table schema information. Specifically I need to get the
>>> column name, data type and size of the column. In SQL there is a
>>> stored procedure that does this 'SP_Help <table Name>. This command
>>> gives me more than I need but I hope the example will help.[/color]
>>
>> You could write a procedure in VBA and DAO to emulate the SQL
>> Server SP_HELP procedure. Here's something to start with:
>>
>> Public Function sp_help() As Boolean
>> sp_help = True
>> Dim db As DAO.Database
>> Dim tbl As DAO.TableDef
>> Dim fld As DAO.Field
>> Dim sStmt As String
>>
>> Set db = CurrentDb()
>> For Each tbl In db.TableDefs
>> Debug.Print ""
>> Debug.Print tbl.Name
>> For Each fld In tbl.Fields
>> Debug.Print " " & fld.Name & " " & fld.Type
>> Next
>> Next
>> Set db = Nothing
>> End Function
>>
>>
>> Have fun!
>>
>> -Mark[/color][/color]

I don't think you'll find table schema information of Access tables in
system tables, so other ways of enquiring for them is probably needed.

There's the .openschema method, which could be called for instance like
this

set rs = objDB.openschema( _
adschemacolumns, array(empty, empty, "yourtable"))

Check the returning field names and contents of this.

--
Roy-Vidar

re: Query to get Table schema

"N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=blue]
> Mark thank you for you response and your quick posting.
>
> I'm not very familiar with DAO I using ADO to access my database. I
> will continue to research you suggestion but to you have an ADO
> example? I could not find and information on the CurrentDB()[/color]

DAO is officially "obsolete," although it is still the preferred
object model if you are working strictly with a Jet (MDB)
database. To use it, you will need to add a reference to DAO
to your project. To do this, press <ALT><F11> to bring up
the VBA dev environment. Then select Tools/References from
the menu, and check "Microsoft DAO 3.6 Object Library."

Here is a fancier version of what I posted yesterday.
This should give you a pretty good idea of how the
DAO object library works. Output from this function is
written to the Debug window (Ctrl + G) in the VBA
dev environment.

-Mark

=================================
Public Function sp_help() As Boolean
sp_help = True
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim sStmt As String
Const constPad01 = 25
Const constPad02 = 15

Set db = CurrentDb()
For Each tbl In db.TableDefs
Debug.Print ""
Debug.Print tbl.Name
For Each fld In tbl.Fields
sStmt = " " & fld.Name & String(constPad01 - Len(fld.Name), " ")
Select Case fld.Type
Case dbLong:
sStmt = sStmt & "long" & " (" & fld.Size & ")"
Case dbText:
sStmt = sStmt & "text" & " (" & fld.Size & ")"
Case dbMemo:
sStmt = sStmt & "memo" & " (" & fld.Size & ")"
Case dbDate:
sStmt = sStmt & "date" & " (" & fld.Size & ")"
Case dbTime:
sStmt = sStmt & "time" & " (" & fld.Size & ")"
Case dbTimeStamp:
sStmt = sStmt & "timestamp" & " (" & fld.Size & ")"
Case dbLongBinary:
sStmt = sStmt & "long binary" & " (" & fld.Size & ")"
Case dbBigInt:
sStmt = sStmt & "BigInt" & " (" & fld.Size & ")"
Case dbBinary:
sStmt = sStmt & "Binary" & " (" & fld.Size & ")"
Case dbVarBinary:
sStmt = sStmt & "VarBinary" & " (" & fld.Size & ")"
Case dbBoolean:
sStmt = sStmt & "Boolean" & " (" & fld.Size & ")"
Case dbByte:
sStmt = sStmt & "Byte" & " (" & fld.Size & ")"
Case dbChar:
sStmt = sStmt & "Char" & " (" & fld.Size & ")"
Case dbDouble:
sStmt = sStmt & "Double" & " (" & fld.Size & ")"
Case dbFloat:
sStmt = sStmt & "Float" & " (" & fld.Size & ")"
Case dbInteger:
sStmt = sStmt & "Integer" & " (" & fld.Size & ")"
Case Else:
sStmt = sStmt & fld.Type & " (" & fld.Size & ")"
End Select

Debug.Print sStmt
Next
Next
Set db = Nothing
End Function

re: Query to get Table schema

Thanks Mark obviously you know a lot more about this than I do.

But I think I'm confused. Are you saying that I need to add the
reference to DAO to the database even if I'm try to access the mdb via
ASP VB coding?

Norris








On Sat, 11 Mar 2006 13:20:49 -0800, "Mark" <nospam@thanksanyway.org>
wrote:
[color=blue]
>
>"N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=green]
>> Mark thank you for you response and your quick posting.
>>
>> I'm not very familiar with DAO I using ADO to access my database. I
>> will continue to research you suggestion but to you have an ADO
>> example? I could not find and information on the CurrentDB()[/color]
>
>DAO is officially "obsolete," although it is still the preferred
>object model if you are working strictly with a Jet (MDB)
>database. To use it, you will need to add a reference to DAO
>to your project. To do this, press <ALT><F11> to bring up
>the VBA dev environment. Then select Tools/References from
>the menu, and check "Microsoft DAO 3.6 Object Library."
>
>Here is a fancier version of what I posted yesterday.
>This should give you a pretty good idea of how the
>DAO object library works. Output from this function is
>written to the Debug window (Ctrl + G) in the VBA
>dev environment.
>
>-Mark
>
>=================================
>Public Function sp_help() As Boolean
> sp_help = True
> Dim db As DAO.Database
> Dim tbl As DAO.TableDef
> Dim fld As DAO.Field
> Dim sStmt As String
> Const constPad01 = 25
> Const constPad02 = 15
>
> Set db = CurrentDb()
> For Each tbl In db.TableDefs
> Debug.Print ""
> Debug.Print tbl.Name
> For Each fld In tbl.Fields
> sStmt = " " & fld.Name & String(constPad01 - Len(fld.Name), " ")
> Select Case fld.Type
> Case dbLong:
> sStmt = sStmt & "long" & " (" & fld.Size & ")"
> Case dbText:
> sStmt = sStmt & "text" & " (" & fld.Size & ")"
> Case dbMemo:
> sStmt = sStmt & "memo" & " (" & fld.Size & ")"
> Case dbDate:
> sStmt = sStmt & "date" & " (" & fld.Size & ")"
> Case dbTime:
> sStmt = sStmt & "time" & " (" & fld.Size & ")"
> Case dbTimeStamp:
> sStmt = sStmt & "timestamp" & " (" & fld.Size & ")"
> Case dbLongBinary:
> sStmt = sStmt & "long binary" & " (" & fld.Size & ")"
> Case dbBigInt:
> sStmt = sStmt & "BigInt" & " (" & fld.Size & ")"
> Case dbBinary:
> sStmt = sStmt & "Binary" & " (" & fld.Size & ")"
> Case dbVarBinary:
> sStmt = sStmt & "VarBinary" & " (" & fld.Size & ")"
> Case dbBoolean:
> sStmt = sStmt & "Boolean" & " (" & fld.Size & ")"
> Case dbByte:
> sStmt = sStmt & "Byte" & " (" & fld.Size & ")"
> Case dbChar:
> sStmt = sStmt & "Char" & " (" & fld.Size & ")"
> Case dbDouble:
> sStmt = sStmt & "Double" & " (" & fld.Size & ")"
> Case dbFloat:
> sStmt = sStmt & "Float" & " (" & fld.Size & ")"
> Case dbInteger:
> sStmt = sStmt & "Integer" & " (" & fld.Size & ")"
> Case Else:
> sStmt = sStmt & fld.Type & " (" & fld.Size & ")"
> End Select
>
> Debug.Print sStmt
> Next
> Next
> Set db = Nothing
>End Function
>[/color]

re: Query to get Table schema

On Sat, 11 Mar 2006 21:29:22 +0100, RoyVidar
<roy_vidarNOSPAM@yahoo.no> wrote:
[color=blue]
>N. Graves wrote in message <0sm512domogklommsor8m28f1r5qilbih9@4ax.com>
>:[color=green]
>> Mark thank you for you response and your quick posting.
>>
>> I'm not very familiar with DAO I using ADO to access my database. I
>> will continue to research you suggestion but to you have an ADO
>> example? I could not find and information on the CurrentDB()
>>
>>
>> This typically the way I access the DB.
>>
>> sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
>> Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"
>>
>> Set objDB = Server.CreateObject("ADODB.Connection")
>> objDB.Open sDBName
>>
>> SQLQuery = "select * from sayings"
>> objDB.execute(SQLQuery)
>>
>> If you have anything else to help I'm very much appreciative
>>
>> On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <nospam@thanksanyway.org>
>> wrote:[color=darkred]
>>>
>>> "N. Graves" <ngraves@REMOVEyahoo.com> wrote:
>>>> Thanks for taking time to read my questions.
>>>>
>>>> I'm looking for a way in Access that I could run a query that would
>>>> return table schema information. Specifically I need to get the
>>>> column name, data type and size of the column. In SQL there is a
>>>> stored procedure that does this 'SP_Help <table Name>. This command
>>>> gives me more than I need but I hope the example will help.
>>>
>>> You could write a procedure in VBA and DAO to emulate the SQL
>>> Server SP_HELP procedure. Here's something to start with:
>>>
>>> Public Function sp_help() As Boolean
>>> sp_help = True
>>> Dim db As DAO.Database
>>> Dim tbl As DAO.TableDef
>>> Dim fld As DAO.Field
>>> Dim sStmt As String
>>>
>>> Set db = CurrentDb()
>>> For Each tbl In db.TableDefs
>>> Debug.Print ""
>>> Debug.Print tbl.Name
>>> For Each fld In tbl.Fields
>>> Debug.Print " " & fld.Name & " " & fld.Type
>>> Next
>>> Next
>>> Set db = Nothing
>>> End Function
>>>
>>>
>>> Have fun!
>>>
>>> -Mark[/color][/color]
>
>I don't think you'll find table schema information of Access tables in
>system tables, so other ways of enquiring for them is probably needed.
>
>There's the .openschema method, which could be called for instance like
>this
>
>set rs = objDB.openschema( _
> adschemacolumns, array(empty, empty, "yourtable"))
>
>Check the returning field names and contents of this.[/color]


Yes I had just been playing with it when I saw your reply. It will
give me the column names but I still need the column data type.

Thank you for your assistance!

Norris

re: Query to get Table schema

N. Graves wrote in message <oqg612lii83gfirksduiaf3nmojb2fclsi@4ax.com>
:[color=blue]
> On Sat, 11 Mar 2006 21:29:22 +0100, RoyVidar
> <roy_vidarNOSPAM@yahoo.no> wrote:
>[color=green]
>> N. Graves wrote in message <0sm512domogklommsor8m28f1r5qilbih9@4ax.com>[color=darkred]
>>>
>>> Mark thank you for you response and your quick posting.
>>>
>>> I'm not very familiar with DAO I using ADO to access my database. I
>>> will continue to research you suggestion but to you have an ADO
>>> example? I could not find and information on the CurrentDB()
>>>
>>>
>>> This typically the way I access the DB.
>>>
>>> sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
>>> Request.ServerVariables("APPL_PHYSICAL_PATH") & "\fpdb\sayings.mdb"
>>>
>>> Set objDB = Server.CreateObject("ADODB.Connection")
>>> objDB.Open sDBName
>>>
>>> SQLQuery = "select * from sayings"
>>> objDB.execute(SQLQuery)
>>>
>>> If you have anything else to help I'm very much appreciative
>>>
>>> On Fri, 10 Mar 2006 21:45:14 -0800, "Mark" <nospam@thanksanyway.org>
>>> wrote:
>>>>
>>>> "N. Graves" <ngraves@REMOVEyahoo.com> wrote:
>>>>> Thanks for taking time to read my questions.
>>>>>
>>>>> I'm looking for a way in Access that I could run a query that would
>>>>> return table schema information. Specifically I need to get the
>>>>> column name, data type and size of the column. In SQL there is a
>>>>> stored procedure that does this 'SP_Help <table Name>. This command
>>>>> gives me more than I need but I hope the example will help.
>>>>
>>>> You could write a procedure in VBA and DAO to emulate the SQL
>>>> Server SP_HELP procedure. Here's something to start with:
>>>>
>>>> Public Function sp_help() As Boolean
>>>> sp_help = True
>>>> Dim db As DAO.Database
>>>> Dim tbl As DAO.TableDef
>>>> Dim fld As DAO.Field
>>>> Dim sStmt As String
>>>>
>>>> Set db = CurrentDb()
>>>> For Each tbl In db.TableDefs
>>>> Debug.Print ""
>>>> Debug.Print tbl.Name
>>>> For Each fld In tbl.Fields
>>>> Debug.Print " " & fld.Name & " " & fld.Type
>>>> Next
>>>> Next
>>>> Set db = Nothing
>>>> End Function
>>>>
>>>>
>>>> Have fun!
>>>>
>>>> -Mark[/color]
>>
>> I don't think you'll find table schema information of Access tables in
>> system tables, so other ways of enquiring for them is probably needed.
>>
>> There's the .openschema method, which could be called for instance like
>> this
>>
>> set rs = objDB.openschema( _
>> adschemacolumns, array(empty, empty, "yourtable"))
>>
>> Check the returning field names and contents of this.[/color]
>
>
> Yes I had just been playing with it when I saw your reply. It will
> give me the column names but I still need the column data type.
>
> Thank you for your assistance!
>
> Norris[/color]

I believe the 12'th column returns the type (rs.fields(11).name /
rs.fields(11).value)

--
Roy-Vidar

re: Query to get Table schema

"N. Graves" <ngraves@REMOVEyahoo.com> wrote:[color=blue]
> Thanks Mark obviously you know a lot more about this than I do.
>
> But I think I'm confused. Are you saying that I need to add the
> reference to DAO to the database even if I'm try to access the mdb via
> ASP VB coding?[/color]

If you are working in Visual Basic, then you will need to add the
DAO reference to your VB project. In the VB6 IDE, you do
this via the Project/References menu.

-Mark

re: Query to get Table schema

In ASP we usually connect to JET(Access) db's with ADO. Roy's advice is
consistent with this. IMO, you would do well to base your soution on
that advice. The results of a similar solution can be seen at
http://ffdba.com/columnsofffdbaaccounts.asp
..

re: Query to get Table schema

On Sat, 11 Mar 2006 23:05:25 +0100, RoyVidar
<roy_vidarNOSPAM@yahoo.no> wrote:
[color=blue]
>objDB.openschema( _[color=green][color=darkred]
>>> adschemacolumns, array(empty, empty, "yourtable"))[/color][/color][/color]
At first I thought you where joking with me. I did not understand
what you where saying. That was the right answer I put what you said
and Lyle and now I have the answer that I was looking for. I have
been looking for this answer actually for a couple of years on and
off.

Thanks you for you help!

re: Query to get Table schema

On 11 Mar 2006 17:27:05 -0800, "Lyle Fairfield"
<lylefairfield@aim.com> wrote:
[color=blue]
>In ASP we usually connect to JET(Access) db's with ADO. Roy's advice is
>consistent with this. IMO, you would do well to base your soution on
>that advice. The results of a similar solution can be seen at
>http://ffdba.com/columnsofffdbaaccounts.asp
>.[/color]
Thanks everyone. With this information, seeing the page and using
Roy's advice I was able to achieve what I was looking for.

Thanks again!!!

Norris

re: Query to get Table schema

Great; to allow for the very small chance that someone else is looking
at this I note that I have changed the page address to:
http://ffdba.com/columns.asp

re: Query to get Table schema

On 12 Mar 2006 07:54:47 -0800, "Lyle Fairfield"
<lylefairfield@aim.com> wrote:
[color=blue]
>Great; to allow for the very small chance that someone else is looking
>at this I note that I have changed the page address to:
>http://ffdba.com/columns.asp[/color]
Page looks great... I'll share my code too:

<%@Language=VBScript %>
<!-- #include file="adovbs.inc"-->
<%
Set objDB = Server.CreateObject ("ADODB.connection")
sDBName = "driver={Microsoft Access Driver (*.mdb)};dbq=" &
Request.ServerVariables("APPL_PHYSICAL_PATH") & "Y O U R M D B"

objDB.Open sDBName

'Set objRS = objDB.Execute(SQLQuery)
set objRS = objDB.openschema(adschemacolumns, array(empty,
empty, "Y O U R T A B L E N A M E "))
If not objRS.EOF Then
Response.Write "Number of columns in table = " &
objRS.fields.count & "<BR>"
Response.Write "<table border=1 align=center><tr>"
for count = 0 to objRS.fields.count-1
Response.Write "<th>" &
objRS.fields(count).name & "</th>"
next
Response.Write "</tr>"
objRS.moveFirst
Do While Not objRS.EOF
for count = 0 to objRS.fields.count-1
Response.Write "<td>" &
objRS(objRS.fields(count).name) & "</td>"
next
Response.Write "</tr>"
objRS.Movenext
Loop
Response.Write "</table>"
End If
objRS.Close
objDB.Close
Set objRS = Nothing
Set objDB = Nothing
%>