I've been searching for hours and hours and can't find what I need.
I'm creating a scripted web application that's designed to manage
multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL,
MySQL) using the same interface. I need to figure out how to create a
recordset that contains a list of all the column properties (column
name, data type, character length) for a specified table. The
MSysObjects table doesn't seem to contain any column information, and
I've seen several places where MS MVP's have said they don't think
Access exposes this information in a table like SQL Server
(syscolumns, information_sch ema.columns) or Oracle (all_tab_column s,
col). If there's a way to create a query, macro, etc. in Access that
will produce a recordset and be accessible by sql or there's a way to
access the information via COM either way will likely be enough for me
to do what I need, but I can't seem to find any clearly described or
succinct information about it.
Thanks,
Isaac
p.s. What is Type 3 in the MSysObjects table? 4 9698
Think Type 3 are subforms
"Isaac Dealey" <in**@turnkey.t o> wrote in message
news:ad******** *************** ***@posting.goo gle.com... I've been searching for hours and hours and can't find what I need.
I'm creating a scripted web application that's designed to manage multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL, MySQL) using the same interface. I need to figure out how to create a recordset that contains a list of all the column properties (column name, data type, character length) for a specified table. The MSysObjects table doesn't seem to contain any column information, and I've seen several places where MS MVP's have said they don't think Access exposes this information in a table like SQL Server (syscolumns, information_sch ema.columns) or Oracle (all_tab_column s, col). If there's a way to create a query, macro, etc. in Access that will produce a recordset and be accessible by sql or there's a way to access the information via COM either way will likely be enough for me to do what I need, but I can't seem to find any clearly described or succinct information about it.
Thanks,
Isaac
p.s. What is Type 3 in the MSysObjects table?
"Isaac Dealey" <in**@turnkey.t o> wrote in message
news:ad******** *************** ***@posting.goo gle.com... I've been searching for hours and hours and can't find what I need.
I'm creating a scripted web application that's designed to manage multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL, MySQL) using the same interface. I need to figure out how to create a recordset that contains a list of all the column properties (column name, data type, character length) for a specified table.
The OpenShema method of the ADODB Connection object is most
likely what you are looking for.
Here's a short example I tested in the Northwind example database.
Obviously you'll have to do some more reading up on everything that's
involved with OpenSchema, but it should be a shove in the right
direction.
<Example Code>
Sub ShowColumnPrope rties(TableName As String)
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Set rs = CurrentProject. Connection.Open Schema _
(adSchemaColumn s, Array(Empty, Empty, TableName))
' For Each fld In rs.Fields
' Debug.Print fld.Name
' Next
With rs
Do While Not .EOF
Debug.Print !COLUMN_NAME
Debug.Print " "; !DATA_TYPE
Debug.Print " "; !CHARACTER_MAXI MUM_LENGTH
Debug.Print " "; !Description
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub
</Example Code>
I think if you use ADO, you can query the DB schema and get all the
details you need - tablenames, field names, types, sizes, properties
and all that...
Jet exposes table schemas via DAO objects. You could use those objects to
create a table with the relevant information, then open a recordset on that
table. Roundabout, but ...!
Something like this (untested):
dim db as database, td as tabledef, fld as field
set db=currentdb()
for each td in db.tabledefs
for each fld in td.fields
debug.print td.name, fld.name, fld.type
next
next
set db=nothing
Of course, that code just prints the details to the debug window. But you
could easily go from there, to saving them into a database table.
HTH,
TC
"Isaac Dealey" <in**@turnkey.t o> wrote in message
news:ad******** *************** ***@posting.goo gle.com... I've been searching for hours and hours and can't find what I need.
I'm creating a scripted web application that's designed to manage multiple databases (MS SQL Server, MS Access, Oracle, PostgreSQL, MySQL) using the same interface. I need to figure out how to create a recordset that contains a list of all the column properties (column name, data type, character length) for a specified table. The MSysObjects table doesn't seem to contain any column information, and I've seen several places where MS MVP's have said they don't think Access exposes this information in a table like SQL Server (syscolumns, information_sch ema.columns) or Oracle (all_tab_column s, col). If there's a way to create a query, macro, etc. in Access that will produce a recordset and be accessible by sql or there's a way to access the information via COM either way will likely be enough for me to do what I need, but I can't seem to find any clearly described or succinct information about it.
Thanks,
Isaac
p.s. What is Type 3 in the MSysObjects table? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Shino |
last post by:
Hi,
Can anyone help with this error: "ORA-00904: invalid column name"?
Thanks!
SQL> create view PPFa as
2 SELECT L.UserID AS LecID, U.Name, U.Email, I.IntakeID, S.UserID
AS StudID
3 FROM User_Lecturer L, User_Student S, TBL_Intake I, Users U
4 WHERE L.UserID=S.InitialSupervisorID And L.UserID=U.UserID And
|
by: Doug Baroter |
last post by:
What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
|
by: CharlesEF |
last post by:
Hi All,
I have run into another problem that is eating my lunch. Should be
simple but I am having one heck of a time. Please look at this SELECT
statement:
SELECT FROM States WHERE ] = "US";
is the SQL column name (because it sarts with 2?).
As the statement is shown I get the error message: Unclosed quotation mark before the character
|
by: UDBDBA |
last post by:
Hi:
we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into
this column manually and also let db2 generate a value for this column.
Given a scenario, how can i find the NEXTVAL for this identity column?
We can do "VALUED NEXTVAL FOR SEQUENCE", but for identity how can it be
done?
|
by: Tim Newton |
last post by:
Hi
I have a vb app that uses an access database to store information. This app
has been distributed to several users. I would like to increase the size of
a field in an access table using my vb app so the users dont have to input
all their data again, what is the best way to do it. I normally use oracle
and would call something like an "alter table modfiy column col1
varchar2(100)". I'm guessing this cannot be done in access and a...
| |
by: Joel Byrd |
last post by:
I've got a 2-column tableless layout and I'm using floats to render the
columns. Here are the basic style definitions for the 2 columns:
#left_col {
width: 59%;
float: left;
clear: left;
}
|
by: Roger |
last post by:
I am trying to do a 'simple' task in a DGV control.
The problem is this.
I have an unbound DGV with all 'AllowUser' options disabled.
RowHeader.Visible=False
ColumnHeader.Text is labelled as needed.
For simplicity the DGV has 7 columns and 15 rows.
The 15 rows are populated and the DGV is made visible.
Everything is just fine.
But now I want rows 6,7 and 11,12 to have different column spacing to the
other rows.
|
by: Hexman |
last post by:
Hello All,
I'd like to find out the best way to add a cb column to a dgv and process efficiently. I see at least two ways of doing it.
-------------------------------
1) Add a cb to the dgv, iterate thru the dgv and update the bound fields if the cb has been checked. Then do the update and accept changes. How do I
access the cb and its checked status? How to iterate thru dgv?
Dim cbSelCol As New DataGridViewCheckBoxColumn
|
by: Neeraj |
last post by:
Hi all.
I have stuck at a strange point.in Database some master table have
identity column and some havenot. Then How can i know at runtimes that
which table have identity column.
According to this i have to write query string.
I got dataset from table.
and
write
ds.Tables.Columns.AutoIncrement
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |