Hi All,

Is it possible to create a Make Table query in access (2.0 and 2003)
that creates the table into a SQL Server database?

Following the steps:
1- Create New Query
2- Set Query Type as Make-Table query
3- Enter table name
4- Click "Another Database", click Browse, then I cannot see anything
about data sources, ODBC, or SQL Server in the drop down box !!! only
few file types like  *.mdb, *.adp, ...etc.

I already setup a ODBC data source.

If this is not supported from the GUI, then what is the SQL syntax? in
other words, SELECT * INTO Table1 IN "???"  FROM Table2

Thanks for reading.

Mourad

Make Table queries are unique to Jet.  You don't say what the database engine 
type is, so I can't directly answer your question, but to use SQL Server as 
an example, you would have to use SQL DDL (Data Definition Language) to 
create a table and its properties.  This functionality also exists in Jet, so 
if you use VBA Help, look at the menu for Microsoft Jet SQL Reference and 
select Data Definition Language, you can get a sense for how it works.  But, 
the syntax defined is for Jet.  SQL Server will have similar but different 
syntax as will other engines.
-- 
Dave Hargis, Microsoft Access MVP


"Mourad" wrote:

> Hi All,
> 
> Is it possible to create a Make Table query in access (2.0 and 2003)
> that creates the table into a SQL Server database?
> 
> Following the steps:
> 1- Create New Query
> 2- Set Query Type as Make-Table query
> 3- Enter table name
> 4- Click "Another Database", click Browse, then I cannot see anything
> about data sources, ODBC, or SQL Server in the drop down box !!! only
> few file types like  *.mdb, *.adp, ...etc.
> 
> I already setup a ODBC data source.
> 
> If this is not supported from the GUI, then what is the SQL syntax? in
> other words, SELECT * INTO Table1 IN "???"  FROM Table2
> 
> Thanks for reading.
> 
> Mourad
>

Thanks Klatuu,

Yes, The DB Engine is SQL Server.


There is a workaround , which is to create the table locally then
export it to SQL Server. But this needs more coding! I'm sure there is
a native  way to do it.

Looking at the references, the syntax is:

  INTO table IN {"ODBC;connect-string"}

However, Access complains with an "ODBC;..." is invalid path!!
Clearly the message is misleading, since this is not a path!!


Regarding using DDL: this is a different thing than Make-Table query!
Because in DDL you have to explicitly mention field names and
types, ..etc. However, in Make-Table query you create the table from
existing table(s)... things work automatically!


Thanks for reading,

Mourad

Actually, it is expecting a path and it expects the table to already by 
created in the destination.

Why not just create the table and just clear the old data out before loading 
the new table in?

-- 
Dave Hargis, Microsoft Access MVP


"Mourad" wrote:

> Thanks Klatuu,
> 
> Yes, The DB Engine is SQL Server.
> 
> 
> There is a workaround , which is to create the table locally then
> export it to SQL Server. But this needs more coding! I'm sure there is
> a native  way to do it.
> 
> Looking at the references, the syntax is:
> 
>   INTO table IN {"ODBC;connect-string"}
> 
> However, Access complains with an "ODBC;..." is invalid path!!
> Clearly the message is misleading, since this is not a path!!
> 
> 
> Regarding using DDL: this is a different thing than Make-Table query!
> Because in DDL you have to explicitly mention field names and
> types, ..etc. However, in Make-Table query you create the table from
> existing table(s)... things work automatically!
> 
> 
> Thanks for reading,
> 
> Mourad
>

Thanks Klatuu,

This form of syntax is not a path, there are other forms that expect a
path, for example: {path | "path" "product" |
[Product;DATABASE=path;| ...]

I just need to get the rigth syntax, I'm sure it is supported.

Thanks,

Mourad

First, IN has two uses.
In a WHERE statement to filter on a list of items:
WHERE [SomeField] IN('Bozo', FooBah', 'Winky', 'Blule')

In a Make Table or Append Query
To identify a destination table:

[SELECT | INSERT] INTO destination IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

Here is how you can do what you want to do.

(copied from VBA Help)

But, it does not support ODBC connections.

Here is what you can do:

docmd.TransferDatabase acExport,"ODBC 
Database","ODBC;Description=ProTrack;DRIVER=SQL 
Server;SERVER=SeverName;APP=Microsoft Office 
2003;WSID=DHARGIS-XP;DATABASE=DbName;Trusted_Connection=Yes",acTable,"MyQueryName","NewTableName"

-- 
Dave Hargis, Microsoft Access MVP

"Mourad" wrote:

> Thanks Klatuu,
> 
> This form of syntax is not a path, there are other forms that expect a
> path, for example: {path | "path" "product" |
> [Product;DATABASE=path;| ...]
> 
> I just need to get the rigth syntax, I'm sure it is supported.
> 
> Thanks,
> 
> Mourad
>

Thanks, Klatuu, for the info

Another way is to set the query property "Dest Connect Str" to
"ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;"

The result SQL query look like:

SELECT * INTO
[ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;].DstTable
FROM SrcTable;

As you see, the IN keyword is not used! But it works, the syntax looks
a little scary though!

Regarding the SELECT..INTO..IN systax, it should contain the ODBC
option: I've found it in page 213 of the book "Microsoft Access 2
Developer's Handbook" by Ken Getz, ..etc. Probably it is an invalid
syntax and they did not test it before publishing it in a book! It
simply not accepted by Access. Or may be my Access 2.0 installation
not up to date.

Your input of the Transferedatabase will certainly help.

Thanks,

Mourad

That works?
Cool.  I guess we both learned something today.
-- 
Dave Hargis, Microsoft Access MVP


"Mourad" wrote:

> Thanks, Klatuu, for the info
> 
> Another way is to set the query property "Dest Connect Str" to
> "ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;"
> 
> The result SQL query look like:
> 
> SELECT * INTO
> [ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;].DstTable
> FROM SrcTable;
> 
> As you see, the IN keyword is not used! But it works, the syntax looks
> a little scary though!
> 
> Regarding the SELECT..INTO..IN systax, it should contain the ODBC
> option: I've found it in page 213 of the book "Microsoft Access 2
> Developer's Handbook" by Ken Getz, ..etc. Probably it is an invalid
> syntax and they did not test it before publishing it in a book! It
> simply not accepted by Access. Or may be my Access 2.0 installation
> not up to date.
> 
> Your input of the Transferedatabase will certainly help.
> 
> Thanks,
> 
> Mourad
>

Certainly, I agree!

Your input is Greatly appreciated!

Best,

Mourad

Glad I could help a bit.
Part of the reason I may not be totally conversent in make table queries is 
that I object to them and I never use them.  There are issues, particularly 
in mdb files with using them.  They are very inefficient and probably one of 
the largest contributors to database bloat.

The way I handle it is as I described earlier.  I use a static, predefined 
defined table and import into that table.  It is rare to export to another 
database, but in that case I use the TransferDatabase method.

But, that's just they way I do it.
-- 
Dave Hargis, Microsoft Access MVP


"Mourad" wrote:

> Certainly, I agree!
> 
> Your input is Greatly appreciated!
> 
> Best,
> 
> Mourad
>

I suppose MS may have implied that one needs to know nothing about SQL-
Server technology in order to use an SQL-Server database through ODBC.
If they did they are wrong. Where is the big red screen that pops up
to the accompaniment of the Dead March from Saul and says, "Hey,
Friends and Neighbours, before you jump into this, here's a minimum
curriculum that you should master."

I don't use ODBC but reading about it makes me think it's pretty
powerful. To select, update, delete data one can use any old Access
query. The ODBC translates this, amazingly well, into something SQL-
Server understands. No brains required. For most Access users and an
increasing number of developers this is just as well.
But TTBOMK, ODBC does not translate something so visceral as a make
table query; we use a Pass-Through query here. The query is passed
through, un-translated, untouched my human-hand, to the SQL-Server and
run there. As the query SQL isn't translated (TTBOMK) we have to know
the correct syntax for SQL-Server and therin lies the challenge.

For a long time I have preached that one of the advantages of the ADP
and ADO is that one is encouraged to learn about the database engine,
in this case, SQL-Server, and one may discover why T-SQL is a million-
gazillion times more powerful than JET/ACE SQL. Well, I am old; I come
from a different age. When I was a boy, besides walking twenty-two
miles to school after milking six hundred cows by hand, I was also
brain-washed into thinking that learning something, anything was
intrinsically good, and about as exciting as life ever got.  Reading
CDMA over the past few years has made me believe that this quaint
notion will die with me.

All of that crap could be shortened to "use a pass-through query".

On Aug 27, 10:20 am, Mourad  wrote:
> Hi All,
>
> Is it possible to create a Make Table query in access (2.0 and 2003)
> that creates the table into a SQL Server database?
>
> Following the steps:
> 1- Create New Query
> 2- Set Query Type as Make-Table query
> 3- Enter table name
> 4- Click "Another Database", click Browse, then I cannot see anything
> about data sources, ODBC, or SQL Server in the drop down box !!! only
> few file types like  *.mdb, *.adp, ...etc.
>
> I already setup a ODBC data source.
>
> If this is not supported from the GUI, then what is the SQL syntax? in
> other words, SELECT * INTO Table1 IN "???"  FROM Table2
>
> Thanks for reading.
>
> Mourad

lol nice lyle ill play the worlds smallest violin for you.

My question would be why are you trying to use a make table query.
The data in one is redundant as soon as you make it.  It is only good
for maybe doing a series of reports on a very complex query that takes
a very long time to run.

Thanks, Lord Kelvan and lyle fairfield for your valuiable input,

It's really worth to converting the Make-Table to pass-through
queries, however, it's worth mentioning the following:

1- The Make-Tables queries already exists, there are simply hundreds
of them! As I mentioned earlier, I'm moving from Access 2.0 back-end
to SQL Server.

2- The Make-Table queries join both Tables and Queries! The joined
queries further join tables and queries, and so on! The joined tables
could reside in both SQL Server and/or the local Access MDB file!! I'm
not sure how the ODBC/SQL Server will handle this! but it's worth to
try!

3- Losing the Query Design view for Pass Through queries is a little
discouraging!


This is a part of migrating an existing application, that was designed
using Access/File Server framework.

I will give it a shot to see how Access will handle this.

Thanks again,

Mourad

I certainly understand your position on this.  But, I will say that upsizing 
to SQL Server is less often a good idea that the times it is done.  There are 
really only two times it is worthy of consideration.
1. The volume of data exceeds the 2GB limit for an Access mdb back end.
2. The data is very sensitive and ULS is not deemed enough security.

For the second reason, there is very little argument there. SQL Server is 
much more secure.

As to the first argument, before considering upsizing, evaluate strategies 
to use multiple back ends.  Can some tables live in on mdb and others in 
another?  Can we group the data by some logical entity where combining the 
data is not or very seldom ever done?

Case in point.  We have an application we use monitor and do billing for 
multi family housing tenants.  It started a few years ago as a fairly small 
operation.  We now have a few hundred clients with multiple regions and 
multiple properties which each have tens to hunreds of tenants.  Obviously, 
this has long ago outrun the 2GB limit.

Rather than upsize, we decided we could split the back ends by client and 
regions.  Reporting never goes beyond that level.  So now, we have 390 mdb 
files each with a name that identifies the client and region.  In the front 
end, when a user selects a client/region, she is automatically relinked to 
that back end.

As to pass through queries.  There are times I use them and times I don't.  
Sometime, a view is faster or more static.  Yes, learning the differences in 
the SQL syntax is daunting, but here is what I have done.  I will first 
create the query as an Access query.  Then I will copy the SQL and go into 
SQL Server Management Studio. Create a new query and work on the syntax until 
it works, then copy the working SQL code back to Access to create the pass 
through.  Also, you can create a query in SQL Server if you have the 
Management Studion, by creating a view.  It gives you a graphical interface.  
You can create a view, copy the SQL and take it into Access to create a pass 
through.

You are correct about pass through queries, Access does not even look at the 
syntax.  Note the name "pass through"

As to Access Projects and ADO.  Don't like 'em, don't believe in 'em, don't 
use 'em.
ADO and adp have had no new work done on them since 2000.  It is also 
interesting to note that even Microsoft, after all their hype about ADO, has 
reverted to making good ole' DAO the default in 2003 and 2007.

-- 
Dave Hargis, Microsoft Access MVP


"lyle fairfield" wrote:

> I suppose MS may have implied that one needs to know nothing about SQL-
> Server technology in order to use an SQL-Server database through ODBC.
> If they did they are wrong. Where is the big red screen that pops up
> to the accompaniment of the Dead March from Saul and says, "Hey,
> Friends and Neighbours, before you jump into this, here's a minimum
> curriculum that you should master."
> 
> I don't use ODBC but reading about it makes me think it's pretty
> powerful. To select, update, delete data one can use any old Access
> query. The ODBC translates this, amazingly well, into something SQL-
> Server understands. No brains required. For most Access users and an
> increasing number of developers this is just as well.
> But TTBOMK, ODBC does not translate something so visceral as a make
> table query; we use a Pass-Through query here. The query is passed
> through, un-translated, untouched my human-hand, to the SQL-Server and
> run there. As the query SQL isn't translated (TTBOMK) we have to know
> the correct syntax for SQL-Server and therin lies the challenge.
> 
> For a long time I have preached that one of the advantages of the ADP
> and ADO is that one is encouraged to learn about the database engine,
> in this case, SQL-Server, and one may discover why T-SQL is a million-
> gazillion times more powerful than JET/ACE SQL. Well, I am old; I come
> from a different age. When I was a boy, besides walking twenty-two
> miles to school after milking six hundred cows by hand, I was also
> brain-washed into thinking that learning something, anything was
> intrinsically good, and about as exciting as life ever got.  Reading
> CDMA over the past few years has made me believe that this quaint
> notion will die with me.
> 
> All of that crap could be shortened to "use a pass-through query".
> 
> On Aug 27, 10:20 am, Mourad  wrote:
> > Hi All,
> >
> > Is it possible to create a Make Table query in access (2.0 and 2003)
> > that creates the table into a SQL Server database?
> >
> > Following the steps:
> > 1- Create New Query
> > 2- Set Query Type as Make-Table query
> > 3- Enter table name
> > 4- Click "Another Database", click Browse, then I cannot see anything
> > about data sources, ODBC, or SQL Server in the drop down box !!! only
> > few file types like  *.mdb, *.adp, ...etc.
> >
> > I already setup a ODBC data source.
> >
> > If this is not supported from the GUI, then what is the SQL syntax? in
> > other words, SELECT * INTO Table1 IN "???"  FROM Table2
> >
> > Thanks for reading.
> >
> > Mourad
> 
>

I think you are likely to find the problems lie with creating SQL Server 
tables from Access via ODBC. With some care, you may be able to create the 
tables in SQL Server, and use Append Queries to add the data records to 
them, instead of Make-Table Queries to create them with data.

 Larry Linson
 Microsoft Office Access MVP

"Mourad"  wrote in message 
news:4706469c-42db-4df4-92e9-d9dbef17b971@i76g2000hsf.googlegroups.com...
> Thanks, Lord Kelvan and lyle fairfield for your valuiable input,
>
> It's really worth to converting the Make-Table to pass-through
> queries, however, it's worth mentioning the following:
>
> 1- The Make-Tables queries already exists, there are simply hundreds
> of them! As I mentioned earlier, I'm moving from Access 2.0 back-end
> to SQL Server.
>
> 2- The Make-Table queries join both Tables and Queries! The joined
> queries further join tables and queries, and so on! The joined tables
> could reside in both SQL Server and/or the local Access MDB file!! I'm
> not sure how the ODBC/SQL Server will handle this! but it's worth to
> try!
>
> 3- Losing the Query Design view for Pass Through queries is a little
> discouraging!
>
>
> This is a part of migrating an existing application, that was designed
> using Access/File Server framework.
>
> I will give it a shot to see how Access will handle this.
>
> Thanks again,
>
> Mourad

Thanks Klatuu and Larry,

The motivation to move back-end to SQL Server is not mainly the file
size! The back-end is already split over many MDB file, with links to
front-end. I would say there are more than one motivation: one of them
is to get Access 2.0 and Access 2003 front-end applications to share
the same back-end data, which is version 2.0 mdb! For some reason we
are unable to run 2.0 and 2003 applications against the same 2.0 mdb
database!!! You may ask why don't upgrade all to 2003? Cannot! because
the application is huge, there are so many front and back-end's
databases, it is just too risky, and requires code freeze for a
while, ...etc.  So one solution was to move the 2.0 back-end into SQL
Server, so front-end's applications connect using ODBC, and so we can
have 2.0 and 2003 apps share same data, then after that we can start
upgrade one front-end at a time. (sorry for the long details)

Larry,

I think Make-Table queries are a maintenance free approach! You don't
have to worry about any structure changes to the underlying joint
tables,  things just work automatically. usually the tables created
using Make-Table query are temporary, in nature, that are used for
reports, ...etc. But I agree it also can be done using the Append
query, after deleting all rows!


I appreciate the tip for creating the right SQL syntax using both
Access and the Management studio.

Thanks,

Mourad

None of these invalidates my suggestion.  If you use Enterprise Manager to 
create the tables in SQL Server, and link them, you should have no problem 
_appending_ the Access records to the SQL tables. And, that will serve 
exactly the same purpose -- creating and populating the SQL Server tables 
with the data that is currently in Access.

And, given that

 (1) you have not been able to use Make-Table queries to create SQL Server 
tables, and

 (2) no one here seems to have done so,

you are quite possibly wasting every minute that you spend trying to find a 
way to do what seems, on the surface to be "an easier way".

Access 2.0 is, long since, "out of support", and there are a number of other 
reasons to bring it up-to-date.  Wishing you did not have to expend time and 
energy to avoid the problems isn't going to make them go away. You can work 
around some of them, but at the expense of making use of Access 2.0 more 
complex. I was very fond of Access 2.0, but unless you retain some old, 
limited-memory machines, plan on running it under a Virtual Machine so you 
can limit memory to a size Access 2.0 can handle.

 Larry Linson
 Microsoft Office Access MVP

"Mourad"  wrote in message 
news:c5eb60a3-301c-40ed-b28f-375877547766@m45g2000hsb.googlegroups.com...
> Thanks Klatuu and Larry,
>
> The motivation to move back-end to SQL Server is not mainly the file
> size! The back-end is already split over many MDB file, with links to
> front-end. I would say there are more than one motivation: one of them
> is to get Access 2.0 and Access 2003 front-end applications to share
> the same back-end data, which is version 2.0 mdb! For some reason we
> are unable to run 2.0 and 2003 applications against the same 2.0 mdb
> database!!! You may ask why don't upgrade all to 2003? Cannot! because
> the application is huge, there are so many front and back-end's
> databases, it is just too risky, and requires code freeze for a
> while, ...etc.  So one solution was to move the 2.0 back-end into SQL
> Server, so front-end's applications connect using ODBC, and so we can
> have 2.0 and 2003 apps share same data, then after that we can start
> upgrade one front-end at a time. (sorry for the long details)
>
> Larry,
>
> I think Make-Table queries are a maintenance free approach! You don't
> have to worry about any structure changes to the underlying joint
> tables,  things just work automatically. usually the tables created
> using Make-Table query are temporary, in nature, that are used for
> reports, ...etc. But I agree it also can be done using the Append
> query, after deleting all rows!
>
>
> I appreciate the tip for creating the right SQL syntax using both
> Access and the Management studio.
>
> Thanks,
>
> Mourad

Thanks, larry, for the feed back,

Just wanted to make a correction, that, I actually COULD find the
right syntax to convert the Make-Table query to create the destination
table in SQL SQL Server! In addition, I've posted the syntax above so
it may benefit everyone! Here is it again:


"... set the query property "Dest Connect Str" to
"ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;"

The result SQL query look like:

SELECT * INTO
[ODBC;DSN=DataSourceName;DATABASE=DBname;Trusted_Connection=Yes;].DstTable
FROM SrcTable;


Thanks again for your comments and feedback,

Mourad