Solved

Problem running query

Posted on 2006-07-03
8
324 Views
Last Modified: 2006-11-18
Hi, everyone.

I would like to know how I can run this query on Microsoft Access using OLE DB. I am using ODBC for doing this job, but I need to do this using OLE DB.

That's an example of query (this works fine on Access):

DELETE FROM tblDATA_Analytics IN '' 'ODBC;DSN=zeus_msde;'

Anyone can help me?

Thanks
0
Comment
Question by:arof
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17033388
the major different may be that OLE DB requires a slightly differnt syntax.  Try this:

DELETE * FROM tblDATA_Analytics IN '' 'ODBC;DSN=zeus_msde;'

are you doing this is a Program, and if so, what programming environment are you using.

AW
0
 

Author Comment

by:arof
ID: 17033455
Dear Arthur_Wood,

Sorry, I forget one detail - this instruction must be DSN less....

This instruction works too, but I can't use ODBC (for performance considerations).

This instruction is one of several instructions executed by a program. But for test it, I open MS Access and paste the query - and it must work.

In this case, it works, but still using ODBC (DSN). I need to use SQLOLEDB.

Thanks
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17035803
Which DB are u using?
You can find more details on connections using this site

http://www.connectionstrings.com/
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:arof
ID: 17038526
I am using an ACCESS DB. I need to run queries from ACCESS to SQL. I will do an example:

- When I open an Access database and go to "Query > SQL Mode" and paste the next query (DELETE FROM tblDATA_Analytics IN '' 'ODBC;DSN=zeus_msde;'), it executes well. On this case, I am using ODBC and DSN.

- I need to execute a query on ACCESS (on the same place, in "Query > SQL Mode") using SQLOLEDB and WITHOUT DSN instead.
   
- In resume, I need the SQL syntax for this query using SQLOLEDB.

ODBC = DELETE FROM tblDATA_Analytics IN '' 'ODBC;DSN=zeus_msde;'
SQLOLEDB = ???

Thanks for the help
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17038548
OleDB connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

or try this without password

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;"


Odbc Connection String

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

or try this without password

"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;"


0
 
LVL 4

Expert Comment

by:VRGultom
ID: 17039876
con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

con.execute(sqlstatement)    //depend on what programming language u use
0
 

Author Comment

by:arof
ID: 17039917
VRGulton,

I'm using only SQL (in this case, T-SQL). I need to represent this query without using programming languages. If I paste this query on Access it works:

DELETE FROM tblDATA_Analytics IN '' 'ODBC;DSN=zeus_msde;'

This example uses ODBC with a DSN. I need an example for run this query using OLE DB and without DSN. I tried to execute this on MS Access:

DELETE FROM tblData_Analytics IN '' "Provider=sqloledb;Data Source=ZEUS;User Id=test;Password=test;"

and this doesn't work.

That's my problem. Could be even a query using OPENROWSET....
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 250 total points
ID: 17040074
you cannot run a query, from the Access SQL mode window, in that manner.  You can only utilize a DSN-less query in VBA code, by opeing a connection (to the SQL Server database), in code, via the Connection object.  That option is NOT applicable from the SQL window in the Query Builder.  There is no way around that restriction/limitation.

If you want to execute the query in VBA,


Dim strSQL as string
Dim cn as adodb.Connection
Set cn = new adodb.connection
cn.connectionstring = "Provider=sqloledb;Data Source=ZEUS;User Id=test;Password=test;"
cn.Open

strSQL = "DELETE * FROM tblData_Analytics "

cn.Execute strSQL

AW
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Has anyone used domo? 1 57
compre toata in where clue oracle 4 58
What does "flush tables;" do exactly? 7 99
Coldusion - DATA insert syntax problem 12 34
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question