Solved

Problem running query

Posted on 2006-07-03
8
322 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now