ADO Query / Command

I'm in the process of downgrading my software to run with ADO.

All I want to do is the equivalent of.

set qdf=dbase.createquerydef("hello","select * from [banana]")

I've got as far a creating a 'Command' against the connections, but can't get it to create a new query/command.
LVL 10
smeggheadAsked:
Who is Participating?
 
tradenutCommented:
You need to use a command object to manipulate the views collection.  The CommandText property holds the sql statement for your view.  Next you need to open a catlog object to actually append the view to the views collection

Here is a sample to create and append a view:

Sub AppendView()
Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim cat as NEW ADOX.Catalog

'Open a connection
'Assuming Jet 4.0
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;       Datasource=C:\MyDatabase\Mydatafile.mdb;"

'Create the command object representing the view
  Set cmd.ActiveConnection = cnn
  cmd.CommandText = "SELECT * FROM Banana"

'Open the catalog object
  Set cat.ActiveConnection = cnn

'Append the view to the catalog
  cat.Views.Append "BananaView", cmd

End Sub

This would create a view called "BananaView" and append it to the Views collection.
0
 
smeggheadAuthor Commented:
I want to create a view / stored procedure / Query on a MS Jet database using ADO.

I can't get it working...

if I execute a ..

CREATE VIEW hello as SELECT * FROM [BANANA]

it says Syntax error in CREATE TABLE statement...

What am I doing wrong... should I be using the command object or views or stored procedures ???
0
 
tradenutCommented:
ack!  the connection string should look like:

cnn.open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\MyDatabase\MyDataFile.mdb;"

I'm assuming you are familar with ADO objects.  I'll be happy to explain a bit further if you like.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
smeggheadAuthor Commented:
tradenut,

I seem to be getting there, but....

I'm using the following code:-

 Dim ThisQry As New adodb.Command
 Dim cat As New ADOX.Catalog

 Set ThisQry.ActiveConnection = SQLConn
 Set cat.ActiveConnection = SQLConn
 ThisQry.CommandText = txt_sql.text
 cat.Views.Append "fred", ThisQry

I'm using DAO 3.51 (which may be the problem)
0
 
smeggheadAuthor Commented:
Oops, didn't say what the problem was..

when it tried to run the Execute command, it crashes saying 'No such interface supported'
0
 
smeggheadAuthor Commented:
It must be near the end of the day... I meant the 'Append' command.

zzzzz zzzzz
0
 
tradenutCommented:
Using DAO 3.51 or did you mean Jet 3.51?
First thing I'd check are your references.
Make sure you have ADO 2.1 and ADO Ext 2.1 referenced.  This may be different using Jet 3.51, but I'm not sure and I have heard of some problems trying to use Jet 3.51 with ADO.

Also you didn't include a connection object in the sample code above.  Lets take a look at that as well.
0
 
tradenutCommented:
Using DAO 3.51 or did you mean Jet 3.51?
First thing I'd check are your references.
Make sure you have ADO 2.1 and ADO Ext 2.1 referenced.  This may be different using Jet 3.51, but I'm not sure and I have heard of some problems trying to use Jet 3.51 with ADO.

Also you didn't include a connection object in the sample code above.  Lets take a look at that as well.
0
 
tradenutCommented:
If you don't have the Microsoft Data Access Components installed (ADO2.1 and other goodies) go to www.microsoft.com/Data and download the MDAC.
0
 
smeggheadAuthor Commented:
Yeah, sorry - Jet 3.51.

And the connection object was set public(ly) - The connection is fine and I'm managing to run SQL againt the connection, so I don't think that's the problem.
0
 
smeggheadAuthor Commented:
I've got it working...

I changed the connect from...

Provider=Microsoft.Jet.OLEDB.3.5.1;

to

Provider=Microsoft.Jet.OLEDB.4.0;

And it worked fine... I'm not too clued up on the difference between jet 3.5 / 4 etc... is 4 the one that ships with MS Access 2000 ???

http://msdn.microsoft.com/library/techart/adocreateq.htm

I will award these points after your next comment....

Thanks.
0
 
tradenutCommented:
Yes, 4.0 shipped with Access 2000.  I'm glad you got it working!
I did some research last night and came to the conclusion that 3.51 does not have a views collection.

From what I was able to determine, Jet 4.0 has a collection for procedures and a collection for views.  The views collection can only be references through ADOX if using MDB files but it is visible when using ADP files.
0
 
smeggheadAuthor Commented:
Hi Tradenut,

I've actually discovered that while using the JET 4 driver, I can just execute a 'CREATE VIEW' command directly, so there's not even a need for the ADOX reference....

Thanks for all your help.

Smegg.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.