Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Problem running query

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
arof
Asked:
arof
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Arthur_WoodCommented:
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
 
arofAuthor Commented:
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
 
rockiroadsCommented:
Which DB are u using?
You can find more details on connections using this site

http://www.connectionstrings.com/
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
arofAuthor Commented:
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
 
rockiroadsCommented:
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
 
VRGultomCommented:
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
 
arofAuthor Commented:
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
 
Arthur_WoodCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now