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 9 18747
I am fairly certain that you can't create a table in Sql Server from an
Access mdb using ODBC. But you can certainly create a table on the
server from an Access mdb using ADO. First make a reference to the
Microsoft ActiveX Data Objects 2.5 (or higher) Library. From a code
module (you have to be in a code module to do this) go to the
Tools/References menu and select Microsoft ActiveX Data Objects 2.5 (or
2.6...) Library
And here is a code sample:
Sub CreateServerTab le()
Dim cmd As New ADODB.Command
cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourServ er;Database=you rDB;UID=steve;p assword=abc;"
'--or if you have windows integrated security set
'cmd.ActiveConn ection = "Provider=SQLOL EDB; Data
Source=yourserv er;Database=you rDB;Trusted_Con nection=Yes"
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Create Table tbl1(rowID int Identity(1,1), fld1
varchar(50), Datefld datetime, fld3 varchar(50))
cmd.Execute
cmd.ActiveConne ction.Close
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Rich P wrote:
I am fairly certain that you can't create a table in Sql Server from
an Access mdb using ODBC. [snip]
I can think if no reason why a passthrough query that executed a CREATE
TABLE statement would not work via ODBC. Assuming of course the user has
permissions to create tables.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
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 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" <mo************ @gmail.comwrote in message
news:47******** *************** ***********@i76 g2000hsf.google groups.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" <mo************ @gmail.comwrote in message
news:c5******** *************** ***********@m45 g2000hsb.google groups.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=DataS ourceName;DATAB ASE=DBname;Trus ted_Connection= Yes;"
The result SQL query look like:
SELECT * INTO
[ODBC;DSN=DataSo urceName;DATABA SE=DBname;Trust ed_Connection=Y es;].DstTable
FROM SrcTable;
Thanks again for your comments and feedback,
Mourad
I just tried
SELECT * INTO
[ODBC;DSN=DataSo urceName;DATABA SE=DBname;Trust ed_Connection=Y es;].DstTab
le
FROM SrcTable;
Sub MakeServerTblUs ingODBC()
Dim strSql As String
strSql = "SELECT * INTO [ODBC;DSN=myDSN; DATABASE=myDB;T rusted_Connecti on
=Yes;].DstTable FROM tbl1;"
DoCmd.RunSql strSql
End Sub
This worked perfectly. I guess I was incorrected about stating that you
could not do this with ODBC. I don't really use ODBC - thus, what do I
know? But this is very cool indeed. I guess ODBC can perform just
about all the actions that ADO can perform. My only issue is the
continuous connection.
Rich
*** Sent via Developersdex http://www.developersdex.com *** This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rich N |
last post by:
I want to move several records from one table to a new table. My
database is set up so that there's the "regular" database, and the
".be" database. In which do I use the Make Table Query?
|
by: JJ |
last post by:
I have a Make Table query which uses a date parameter. Each time the
user runs the query they will be prompted for a date. The table which
is created should be named based on the date which they entered. For
example: The query will retieve all invoices dated xxxxx, the user
will may supply the date of Jan 1 2004. It should create a table
called "Jan 1 2004 Invoices" (or something like that). Each table
created needs to be saved in the...
|
by: Oreo Bomb |
last post by:
I have a secured database that contains a Read-Only group. This group
has permissions to view reports, but cannot add, edit, or delete any
DB objects. One of the reports the group needs access to is generated
with a Make-Table Query. Since the Make-Table Query deletes the
previous table it created everytime it's ran, this poses a problem
since the Read-Only group doesn't have the permission to delete
objects. This causes the report...
|
by: LesM |
last post by:
This is a change of behaviour between Access 2000 SP3 and Access 2002
SP3.
I have Progress table that is linked via ODBC into Access using OpenLink
Lite for Progress 9.0b. For over a year, using Access 2000 under Win98,
I have been running a Make Table or Append query against this ODBC
table and producing an extracted Access table. Any fields that are NULL
on the source ODBC table show as NULL on the output table. The query
also has an...
|
by: Doc |
last post by:
Per earlier post, I am trying to save 'out' production data from a
program called Solomon - basically (I think) this was /is an Access/Sql
based program.
We are updating to different application and I need to bring over ONLY
certain DATA.
When I did an ODBC link to the DB all the tables are present.
I linked the three or four tables and have my fields (these are the ones
I want to modify for LATER import into the new application)
| |
by: ken |
last post by:
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.
Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...
Anything else I could use other then Currentdb.execute?
|
by: Laetitia Clark |
last post by:
Is it possible to force a Decimal type field from an ODBC source to
become a Double type field in the destination table when using a Make
Table query ?
Thanks in advance.
Laetitia
South Africa
|
by: Robertf987 |
last post by:
Hi, I'm a bit stuck with an access database. I'm using access 2000 if that's any help.
Right, it's 3:40am right now and I'm rather tired, but I *hope* this makes sense.
I have a table which contains the main data of the database, records of information of grants. For each financial year, the old data needs taking out of the table. Basically I have two options: delete the old data from the year before, or back it up in a new table. I'd rather...
|
by: arcingmad |
last post by:
Hi I am using the following code in a button click event to run a query stored in a .mdb file from an external geographic program ArcGIS via a jet adodb connection
I have the need to run an existing make table query, my problem is it will over write an existing table. i.e when run in access a warning that you are about to overwrite an existing table will be shown and you will be prompted to accept or reject. When run via the external program...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |