yes, You dont have permission to read MSysObjects : p
heres how to change it...
go to tools > options
under the view tab, check the System Objects checkbox and hit OK
once thats done go to Tools > Security > User and Group Permissions
highlight the MSysObject in the right window and check the Read checkbox and hit OK
you can leave the System Objects check box checked under Tools > Options > View tab if you want or you can take it back off... YOu will see some extra tables in table view if you leave it on of course. Either way the permissions are set once you do the first 2 steps
----- Original Message -----
From: Qasim Rasheed
To: CF-Talk
Sent: Wednesday, August 11, 2004 12:20 PM
Subject: Re: SQL Question (I'm bored)
Just out of curiosity, how have you managed to read MSysobjects? When
I tried it on my local machine I got the following error
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Record(s) cannot be read; no read permission
on 'MsysObjects'
Any thoughts?
----- Original Message -----
From: Ewok <[EMAIL PROTECTED]>
Date: Wed, 11 Aug 2004 11:31:34 -0400
Subject: SQL Question (I'm bored)
To: CF-Talk <[EMAIL PROTECTED]>
anyone know how to use MSysObjects to get the field size of a column
and the primary key of a table in an access database?
I've managed to pull all tables from a datasource then pull all column
names from those tables and data types of all the columns. (granted
they are not access datatypes but thats ok) this will be to read all
of my access DBs and build a script I can copy and paste to build the
same table in SQL Server
thsi is what I've got so far...
<cfquery name="tables" datasource="TestDataSource">
SELECT MSysObjects.Name AS TableNames
FROM MsysObjects
WHERE (MSysObjects.Type = 1)
AND (Left(MSysObjects.Name,1) <> '~')
AND (Left(MSysObjects.Name,4) <> 'Msys')
ORDER BY MSysObjects.Name
</cfquery>
<cfoutput>
<cfloop query="tables">
<cfquery name="columns" datasource="TestDataSource" maxrows="1">
SELECT * FROM #tables.tablenames#
</cfquery>
[<b>#tables.TableNames#</b>]<br>
<cfloop list="#columns.ColumnList#" index="col">
<cfset metadata = columns.getMetaData()>
<cfset DataType =
lcase(columns.getColumnTypeName(metadata.getColumnType(columns.findColumn(col))))>
#lcase(col)# - #DataType#<br>
</cfloop>
<br>
</cfloop>
</cfoutput>
I haven't gotten it to tell me when the column is a primarykey or the
field size ... like a field I create with Text(50), I need to read
that 50
any thoughts?________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]