"jc" <jc********@aanet.com.auwrote in news:1174562162.266662.142910
@l77g2000hsb.googlegroups.com:
Hello, a question for the MS Access community, from someone who
programs with SQL in SQL2000. I currently create a table with varying
column names and data within SQL2000. I then need to reproduce the
same in a Access file DB.
The normal functions available with SQL2000 such as OpenRowSet are
good for Insert, Select etc., but not the simple "DDL" task of
creating a table.
Can another help/suggest ?
Regards JC.....
Suggestions ...
I would try asking my MS-Sql management utility to script the SQL table
as CREATE. I would copy the T-SQL script and try running it in JET.
Would the syntax be compatible? I don't know. My guess is that it would
need minor revisions.
If I had some extra spare time to experiment I would try linking the
Access/JET db to the SQL server and see if I could modify the CREATE SQL
string to act on the linked server (actually the JET db). The SQL Server
Management Studio Utility provides a search and replace function for T-
SQL editing; I find this very powerful and it cuts down my work by maybe
90% when I want to duplicate SPROCS or VIEWS for tables or databases
other than those for which they were designed (of course, it reduces
syntax errors too).
You can, of course, set up an ADP connected to the SQL db/server and from
an MDB, simply import the sql tables. (The tables are SQL_Server in the
ADP, but Access converts them to JET in the MDB. Of course, this may not
be efficient for mega-tables.) The ADP can have other uses or, just be
there as a utility for you to import SQL tables. I have code somewhere to
do the import. I used to do this daily to back up my data from a remote
SQL db (because the provider of the SQL service charged $90.00 for each
backup). This procedure does not take a long time and can run silently,
invisibly and regularly if called from the Windows scheduler.
--
lyle fairfield
Ceterum censeo Redmond esse delendam.