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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ???
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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)
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'
smeggheadAuthor Commented:
It must be near the end of the day... I meant the 'Append' command.

zzzzz zzzzz
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.
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.
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.
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.
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.
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.