sp_columns for microsoft access

[woddel]

hello all

in sql server, i can us the stored procedure sp_columns to get the table
information (rownames, type, length etc) of a 'unknown' table. now, i need to
write a routine to import tables from microsoft access databases into sql
server. but sp_columns does not work on access.

how can i tickle that information out of access using coldfusion and
sql-queries? anybody any wiser than me? :)

cheerios

lukas @ switzerland

[philh]

There are no straight SQL queries to return Access table schema; it's not available through the ODBC driver. You have to use VB/ADO to get the info.

[efecto747]

It can be done but its pretty messy - firstly you can get table names from the
MSysObjects table - this is normally hidden but you can show it through the
options menu. The following query will give you all table names:

SELECT Name
FROM MSysObjects
WHERE Type = 1

You then need to select one row of data (use maxrows=1) from each table and
serialise it using wddx. This reveals column names in their correct order and
also shows the data types.

Below is a snippet of code I used to use to get this info from an Access 97
database the field names are stored as a list in #fieldnames# and data types in
#fieldtypes# - hope this helps.

cheers.



<cfquery name="qryColumns" datasource="#dsn#" maxrows="1">
SELECT * FROM #tablename#
</cfquery>

<!--- convert any dates so that wddx serialisation doesn't crash.. --->
<cfloop index="cname" list="#qryColumns.ColumnList#">
<cfif IsDate(Evaluate("qryColumns."& cname))>
<cfset "qryColumns.#cname#" = DateFormat(Evaluate("qryColumns."&
cname),"yyyy-mm-dd") & " " & TimeFormat(Evaluate("qryColumns."&
cname),"HH:MM:SS")>
</cfif>
</cfloop>

<!---
make into wddx packet so that column names are put into correct order
and also so we can identify the data type of each column
--->
<cfwddx action="cfml2wddx" input="#qryColumns#" output="qWDDX">
<cfset fieldnames = "">
<cfset fieldtypes = "">
<cfset nextpos = FindNoCase("<field name='", qWDDX, 1)>
<cfloop condition="nextpos neq 0">
<cfset nextpos = nextpos + 13>
<cfset endpos = FindNoCase("'", qWDDX, nextpos)>
<cfset fieldlen = endpos - nextpos>
<cfset fieldnames = fieldnames & Mid(qWDDX, nextpos, fieldlen) & ",">
<cfset nextpos = endpos + 3>
<cfset endpos = FindNoCase(">", qWDDX, nextpos)>
<cfset fieldlen = endpos - nextpos>
<cfset fieldtypes = fieldtypes & Mid(qWDDX, nextpos, fieldlen) & ",">
<cfset nextpos = FindNoCase("<field name='", qWDDX, nextpos)>
</cfloop>