Solved

Problem running query

Posted on 2006-07-03
8
328 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

Technology Partners: 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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

726 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