Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 18th, 2004, 11:37 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default passthrough query

Hello, i would like to run following script in passthrough query:
"select * into #temp1 from tableA", However, it doesn't return any rows, anyideal? Thanks

Reply With Quote
  #2 (permalink)  
Old November 18th, 2004, 11:29 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

are you connecting to SQL as pass through query?



Jaime E. Maccou
Applications Analyst
Reply With Quote
  #3 (permalink)  
Old November 22nd, 2004, 12:49 PM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, thank Jemacc.

Reply With Quote
  #4 (permalink)  
Old November 23rd, 2004, 11:47 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

The information you need is in the help file for access

In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
Without adding tables or queries, click Close in the Show Table dialog box.
On the Query menu, point to SQL Specific, and then click Pass-Through.
On the toolbar, click Properties to display the query property sheet.
In the query property sheet, set the ODBCConnectStr property to specify information on the database to which you want to connect. You can type the connection information, or click Build , and then enter information about the server you're connecting to.
When you are prompted to save the password in the connection string, select Yes if you want the password and logon stored in the connection string information.

If the query isn't the type that returns records, set the ReturnsRecords property to No.
In the SQL Pass-Through Query window, type your pass-through query (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.).
For details on the syntax for your query, see the documentation for the SQL database server to which you're sending the query.

To run the query, click Run on the toolbar. (For a pass-through query that returns records, you can click View on the toolbar, instead.)
If necessary, Microsoft Access prompts you for information about your SQL server database.

Notes

If you don't specify a connection string in the ODBCConnectStr property, or if you delete an existing string, Access uses the default string "ODBC;". With this setting, Access prompts you for connection information each time you run the query.
Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.
Create a make-table query (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in it by copying records from an existing table.) based on the pass-through query.
How?

Create a query, selecting the tables or queries that contain the records you want to put in the new table.
How?

In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click Close.
Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
To view the query's results, click View on the toolbar.
In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), click the arrow next to Query Type on the toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), and then click Make Table. The Make Table dialog box appears.
In the Table Name box, enter the name of the table you want to create or replace.
Do one of the following:
Click Current Database.

Click Another Database, and then either type the path of the database where you want to put the new table or click Browse to locate the database.

Click OK.
Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want in the new table.
In the Criteria cell for the fields that you've dragged to the grid, type the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
To preview the new table before you create it, click View on the toolbar. To return to query Design view and make changes or run the query, click View on the toolbar again.
To create the new table, click Run on the toolbar.
Note The data in the new table you create does not inherit the field properties or the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) setting from the original table.

In the make-table query, include all fields from the pass-through query by dragging the asterisk (*) to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).
Run the make-table query.
How?

Do one of the following:

 Run a select or crosstab query

When you open a select (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) or crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.), Microsoft Access runs (executes) the query for you and shows the results in Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects.
Click the query you want to open.
Click Open on the Database window toolbar.
 Run an action query

Unlike select (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) and crosstab queries (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.), you can't view the results of an action query (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Database window.) by opening it in Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.). However, in Datasheet view you can preview the data that will be affected when you run the action query.

Caution It's a good idea to make a copy of the data you are changing or moving in an action query, in case you need to restore the data to its original state after running the action query.

Open the action query in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.).
To preview the records that will be affected in Datasheet view, click View on the toolbar and check the records. For each action query, you will see the following: For this query The datasheet displays
Update The fields to be updated.
Delete The records to be deleted.
Make-table The fields to be included in the new table.
Append The records to be added to another table.

To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
Click Run on the toolbar to run the query.
Note To stop a query after you start it, press CTRL+BREAK.

When you run the make-table query, you get one table for each result. The first table created has the name defined in the query; subsequent tables have that name concatenated with a sequential number starting at 1.


Jaime E. Maccou
Applications Analyst
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
I solved insert query.now see this Update Query. amit_mande@yahoo.com VB.NET 2002/2003 Basics 2 September 21st, 2006 12:48 AM
passthrough password BlueSkies Access 2 August 28th, 2006 06:29 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Error on Make-Table Query In Union Query rylemer Access 1 August 20th, 2003 07:42 PM



All times are GMT -4. The time now is 01:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.