Create Access Tables with a Pass Through Query from SQL2000 with SQL

**Gord** · Mar 22 '07, 01:05 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 with SQL

On Mar 22, 7:16 am, "jc" <jc_user...@aan et.com.auwrote:

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.....

Have you investigated DTS to see if it will do what you need?

jc · Mar 22 '07, 01:25 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 with SQL

>
Have you investigated DTS to see if it will do what you need?

Yes. DTS can generate SQL code (or it can be used) but it is no
flexible. It creates a DTS module but the Columns names become hard
coded within. Can it take parameters ? Not from what I have found.

Regards JC.......

**lyle fairfield** · Mar 22 '07, 01:55 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 with SQL

"jc" <jc_usernet@aan et.com.auwrote in news:1174562162 .266662.142910
@l77g2000hsb.go oglegroups.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.

**Tim Marshall** · Mar 22 '07, 02:15 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 withSQL

lyle fairfield wrote:

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).

I know you can do this with Oracle. I don't know the specifics, but a
four or five years ago I had a developer working on an Access/Oracle app
who knew nothing whatsoever about Oracle DDL (and stupidly refused to
learn - how someone like that could call herself a developer when she
couldn't even create a table in the back end with which she was
working.... grrrr, down blood pressure, down, boy!). She somehow
mysteriously (to my mind) created tables using Access jet alone.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

**Gord** · Mar 22 '07, 06:15 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 with SQL

>She somehow mysteriously (to my mind) created tables using Access jet

>alone.

I suspect that she created the table in Access and then used

File Export...

with ODBC to create the Oracle table. That's clearly not the best way
because the data types can get "subtly altered". For example, Long
Integer columns in Access become VARCHAR2(20) in Oracle. But who
knows, maybe the application didn't need numbers anyway....

On Mar 22, 9:58 am, Tim Marshall
<TIM...@PurpleP andaChasers.Moe rtheriumwrote:

lyle fairfield wrote:

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).

>
I know you can do this with Oracle. I don't know the specifics, but a
four or five years ago I had a developer working on an Access/Oracle app
who knew nothing whatsoever about Oracle DDL (and stupidly refused to
learn - how someone like that could call herself a developer when she
couldn't even create a table in the back end with which she was
working.... grrrr, down blood pressure, down, boy!). She somehow
mysteriously (to my mind) created tables using Access jet alone.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

**Larry Linson** · Mar 22 '07, 08:05 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 with SQL

"jc" <jc_usernet@aan et.com.auwrote

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 ?

Assuming you have or can obtain appropriate permissions, in an Access MDB,
link the SQL Server Table via ODBC, use a MakeTable Query (use the
QueryBuilder to create a SELECT query selecting all the fields you desire
from the SQL Server Table, then on the Menu: Query | Make Table).

Anyone who would write SQL or DDL from scratch when they could do the same
thing, simply, with point-and-click in the Query Builder is, IMNSHO, an "SQL
masochist". < :-) I include the smiley only because I have a number of SQL
masochists among my friends and try to avoid offending them overly much>.

Larry Linson
Microsoft Access MDB

**Tim Marshall** · Mar 22 '07, 08:15 PM

Re: Create Access Tables with a Pass Through Query from SQL2000 withSQL

Gord wrote:

>>She somehow mysteriously (to my mind) created tables using Access jet
>>alone.

>
I suspect that she created the table in Access and then used
>
File Export...

That sounds like it was probably it.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

Create Access Tables with a Pass Through Query from SQL2000 with SQL

Create Access Tables with a Pass Through Query from SQL2000 with SQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment