Solved

Problem running query

Posted on 2006-07-03
8
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

740 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