Setting SQL Server query processing options via Access/VBA 
Author Message
 Setting SQL Server query processing options via Access/VBA

To anyone that can help,

I have situation where I am using Access 97 as a front-end to a SQL Server
6.5 database.  I am using ODBC to connect the Access interface to the SQL
Server back-end.

I now have a particular need to send to send SQL server-specific commands to
the SQL Server from Access.  I would like to be able to send something like
below to the SQL Server using Access and/or VBA:

set IDENTITY_INSERT table1 ON

INSERT INTO table1 (field1, field2)
 SELECT field1, field2
 FROM dbo_old_table1

set IDENTITY_INSERT table1 OFF

table1's field1 column is an INTEGER IDENTITY column

Now I have found that this is not possible using standard Access query
definitions.  Is there any technique where this sort of thing can be
achieved.  If it can not be done via ODBC, then what are the alternatives?
Can Access communicate to a SQL Server via any other means than ODBC?  Can
it be done in VBA?

Thanks in advance,

Jeremy Anderson



Fri, 27 Apr 2001 03:00:00 GMT  
 Setting SQL Server query processing options via Access/VBA

You can always create a pass-through query using exactly the SQL you want and it
gets sent to the server unchanged without any pre-processing by DAO/Access.

When creating the Query, right click the query window and select SQL View. Type
in the Server SQL. Right click the window once more and select the Pass-Through
option. Save the query as say  PT-Query

In VBA:

dim qry as querydef
dim rs as recordset

set qry = currentdb.querydefs!PT_Query
set rs = qry.openrecordset (......)

where .... are options to execute the query - see Access Help -> Openrecordset
for details.

Hope this helps

Prodosh

Quote:

> I now have a particular need to send to send SQL server-specific commands to
> the SQL Server from Access.  I would like to be able to send something like
> below to the SQL Server using Access and/or VBA:

> set IDENTITY_INSERT table1 ON

> INSERT INTO table1 (field1, field2)
>  SELECT field1, field2
>  FROM dbo_old_table1

> set IDENTITY_INSERT table1 OFF

> table1's field1 column is an INTEGER IDENTITY column



Fri, 27 Apr 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Accessing the Command bar options via VBA

2. Sql - Dates query to SQL Server and access

3. Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table

4. VB Sql Server Via ADO Problem: [Microsoft][ODBC Sql Server Driver]

5. Setting Excel cell formula via Access VBA

6. Exporting Access Query SQL via WSH

7. Query an access database via SQL in VB6

8. Executing MS SQL Server 2000 stored procs via MS Access 2000

9. Trouble connecting to SQL Server 7 via Access Project

10. sql-server access via visual basic

11. Problem accessing SQL Server with VB5 via DAO

12. Accessing MS SQL Server Text Fields via RDO

 

 
Powered by phpBB® Forum Software