Board index MS SQL Server

Setting SQL Server query processing options via Access/VBA

Setting SQL Server query processing options via Access/VBA

Post by Jeremy Anderso » Tue, 10 Nov 1998 04:00:00



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

 
 
 
Top

Setting SQL Server query processing options via Access/VBA

Post by Brian Mora » Tue, 10 Nov 1998 04:00:00


search for "pass through" in the Access VBA help files. Pass throuhg queries
allow you to specify backedn specific syntax.

--
Brian Moran
Director of Database Technology
Spectrum Technology Group
MCSE, MCSD, SQL Server MVP
Windows NT Magazine Columnist
President, Capital Area
SQL Server Users Group

Check out my monthly SQL column
in Windows NT Magazine!


>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



 
 
 
Top

Setting SQL Server query processing options via Access/VBA

Post by Buddy Ackerma » Thu, 12 Nov 1998 04:00:00


Jeremy,  when using MS Acces or VB I like to call stored procedures via pass
through queries as much as possible.  That way the entire transaction is
processed and you don't have to worry about the application or PC crashing in
the middle of your group of SQL statements and leaving a process half finished.
If you ever go to VB you can use ADO which will allow you to create a
transaction at the server which can be rolled back in case the application
crashes before the transaction is complete.

Example (VB or VBA):
    MyDB.Execute "my_stored_proc", dbSQLPassThrough

Where MyDB is a DAO type database object.

--
Buddy Ackerman


> 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


 
 
 
Top

1. Setting a process priority in a SQL pass through query from MS Access

Does anyone know of a method to set a process priority on a SQL pass
through query from MS Access to Oracle 8 on an NT box?  I have several
people using an interactive application quite happily, until someone
decides to run a query that summarizes all the data for the past few
years or some such thing.  I would like to be able to set the priority
for such queries to low.  Currently, the CPU seems to be taken over by
the query and prevents other users from accessing the DB through the
application.  I have heard that one can create a user and assign their
priority to low or perhaps using Oracle's Advanced Queueing.  Any
ideas? Thanks!

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Workstation License Matters

3. How to set FIPS_FLAGGER option in Access 97/SQL Server 7.0 with ODBC

4. Still time to benefit: Testing Computer Software (TCS2000) Conference

5. Add a New User to a SQL-Server 2000 via vba and adp

6. Will Ado 2.5 Install if Higher version installed!

7. Set field size via VBA

8. pgsql/src/bin/initdb initdb.sh

9. MyODBC, MySQL and VB - Setting ODBC options via code

10. Calling query using VBA function via ASP

11. setting system date via transact sql in SQL server 2000

12. Getting a SQL Server option setting

13. setting server options hangs sql setup


All times are UTC
Board index