Cannot view queries created using ADOX in Access 2000 interface

Cannot view queries created using ADOX in Access 2000 interface

Post by Shane G. Brodi » Wed, 18 Jun 2003 05:10:48



I have a VB 6.0 program that generates a Jet 4.0 database for me including
tables and queries.  The queries are available for use programatically, but
are not visible in the MS Access interface!  Is this a bug, feature or an
omission in my code?  A snippet of the query creation code follows:

'***************************
    'Generate Queries
    Set objCmd = New Command
    objCmd.ActiveConnection = objCat.ActiveConnection
    objCmd.CommandText = "SELECT job_application_tb.app_id,
job_application_tb.app_dt, job_application_tb.company_id,
company_tb.company_name, job_application_tb.app_description,
job_application_tb.status_id, status_tb.status_description FROM
(job_application_tb LEFT JOIN company_tb ON job_application_tb.company_id =
company_tb.company_id) LEFT JOIN status_tb ON job_application_tb.status_id =
status_tb.status_id ORDER BY job_application_tb.app_dt;"
    objCat.Procedures.Append "application_vw", objCmd
    objCat.Procedures.Refresh
    RaiseEvent CreationStatus("application_vw Created")

'**************************

Regards

Shane Brodie

 
 
 

Cannot view queries created using ADOX in Access 2000 interface

Post by Tore Bostru » Wed, 18 Jun 2003 07:36:56


"PRB: Views Created Using ADOX Are Not Visible in Access 2000" (Q246213)

HTH,
Tore.



Quote:> I have a VB 6.0 program that generates a Jet 4.0 database for me including
> tables and queries.  The queries are available for use programatically,
but
> are not visible in the MS Access interface!  Is this a bug, feature or an
> omission in my code?  A snippet of the query creation code follows:

> '***************************
>     'Generate Queries
>     Set objCmd = New Command
>     objCmd.ActiveConnection = objCat.ActiveConnection
>     objCmd.CommandText = "SELECT job_application_tb.app_id,
> job_application_tb.app_dt, job_application_tb.company_id,
> company_tb.company_name, job_application_tb.app_description,
> job_application_tb.status_id, status_tb.status_description FROM
> (job_application_tb LEFT JOIN company_tb ON job_application_tb.company_id
=
> company_tb.company_id) LEFT JOIN status_tb ON job_application_tb.status_id
=
> status_tb.status_id ORDER BY job_application_tb.app_dt;"
>     objCat.Procedures.Append "application_vw", objCmd
>     objCat.Procedures.Refresh
>     RaiseEvent CreationStatus("application_vw Created")

> '**************************

> Regards

> Shane Brodie


 
 
 

Cannot view queries created using ADOX in Access 2000 interface

Post by Shane G. Brodi » Wed, 18 Jun 2003 07:42:56


Thanks muchly:

Not the answer I was hoping for, but it was the answer I expected ...

I have written code in the past where I had to interchangeably use DAO and
ADO/X to do what I wanted, I was hoping in this case to say goodbye to DAO.
Doh!!!!

Regards

Shane

> "PRB: Views Created Using ADOX Are Not Visible in Access 2000" (Q246213)

> HTH,
> Tore.



> > I have a VB 6.0 program that generates a Jet 4.0 database for me
including
> > tables and queries.  The queries are available for use programatically,
> but
> > are not visible in the MS Access interface!  Is this a bug, feature or
an
> > omission in my code?  A snippet of the query creation code follows:

> > '***************************
> >     'Generate Queries
> >     Set objCmd = New Command
> >     objCmd.ActiveConnection = objCat.ActiveConnection
> >     objCmd.CommandText = "SELECT job_application_tb.app_id,
> > job_application_tb.app_dt, job_application_tb.company_id,
> > company_tb.company_name, job_application_tb.app_description,
> > job_application_tb.status_id, status_tb.status_description FROM
> > (job_application_tb LEFT JOIN company_tb ON

job_application_tb.company_id
Quote:> =
> > company_tb.company_id) LEFT JOIN status_tb ON

job_application_tb.status_id

- Show quoted text -

Quote:> =
> > status_tb.status_id ORDER BY job_application_tb.app_dt;"
> >     objCat.Procedures.Append "application_vw", objCmd
> >     objCat.Procedures.Refresh
> >     RaiseEvent CreationStatus("application_vw Created")

> > '**************************

> > Regards

> > Shane Brodie

 
 
 

Cannot view queries created using ADOX in Access 2000 interface

Post by Tore Bostru » Wed, 18 Jun 2003 08:09:39


... then say goodbye to Jet... :->

MSDE and MySQL may both be good and inexpensive (free) choices.

HTH,
Tore.



> Thanks muchly:

> Not the answer I was hoping for, but it was the answer I expected ...

> I have written code in the past where I had to interchangeably use DAO and
> ADO/X to do what I wanted, I was hoping in this case to say goodbye to
DAO.
> Doh!!!!

> Regards

> Shane


> > "PRB: Views Created Using ADOX Are Not Visible in Access 2000" (Q246213)

> > HTH,
> > Tore.



> > > I have a VB 6.0 program that generates a Jet 4.0 database for me
> including
> > > tables and queries.  The queries are available for use
programatically,
> > but
> > > are not visible in the MS Access interface!  Is this a bug, feature or
> an
> > > omission in my code?  A snippet of the query creation code follows:

> > > '***************************
> > >     'Generate Queries
> > >     Set objCmd = New Command
> > >     objCmd.ActiveConnection = objCat.ActiveConnection
> > >     objCmd.CommandText = "SELECT job_application_tb.app_id,
> > > job_application_tb.app_dt, job_application_tb.company_id,
> > > company_tb.company_name, job_application_tb.app_description,
> > > job_application_tb.status_id, status_tb.status_description FROM
> > > (job_application_tb LEFT JOIN company_tb ON
> job_application_tb.company_id
> > =
> > > company_tb.company_id) LEFT JOIN status_tb ON
> job_application_tb.status_id
> > =
> > > status_tb.status_id ORDER BY job_application_tb.app_dt;"
> > >     objCat.Procedures.Append "application_vw", objCmd
> > >     objCat.Procedures.Refresh
> > >     RaiseEvent CreationStatus("application_vw Created")

> > > '**************************

> > > Regards

> > > Shane Brodie

 
 
 

Cannot view queries created using ADOX in Access 2000 interface

Post by Shane Brodi » Thu, 19 Jun 2003 00:30:38


True, I have used both extensively.  

However, packaged deployment of MSDE or MySQL are both a lot more work
(and a whole lot more disk space), and performance on older Pre-Pentium
II clients (with MSDE at least) becomes an issue.

In a perfect world ... everyone should have a P4, 1 GB of RAM, 1 TB disk
space, Windows 2000 Pro(or Windows XP Pro)  and SQL Server 2000.

But, my primary (I also have a 933 mhz Celeron notebook) development
machine is a P233 MMX, 256 MB RAM, 12 GB hard disk, running XP Pro, SQL
Server 2000, MS Office 2000, Visual Studio 6.0, Visual Studio .Net,
etc., etc ...

"If I can make it sing at 233 mhz ... it'll work blazingly anywhere!"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Views Created Using ADOX Are Not Visible in Access 2000

Hi all,

I was disgusted learning this from Microsofts Knowledge Base:

Id: Q246213

When using Microsoft ActiveX Data Objects (Microsoft ADO Ext. 2.1 for
DDL and Security) to Append a new query (View) to an Access 2000
database, the query icon does not appear among the database queries when
you start Access 2000.

I built an application which uses a Jet database through ADO/ADOX.
Although I try to give the users as much functionality as possible,
there all always cases he qickly wants to get something out of the data
the application doesn't offer. So I documented the database very well to
offer the user the full power of having a database.

Dear people at Microsoft, I am very consciuos about what information I
disclose and what information I want to hide. So leave the decision to
the application/database programmer what views or tables he wants to
hide from the user.
I strongly hope there will be a possiblity to make these queries visible
in one of the next service packs.
By the way, there is no need to hide these queries, if I copy the SQL
statement in Access and paste it into my application source code.

Bye
Lars

2. RESTORE FILELISTONLY

3. Creating views/procedures using ADOX in Access DB

4. Query timeouts

5. cannot create new views with access 2000

6. sqlldr on unix (security...)

7. Creating Access Queries using ADOX

8. 11@@##!!&&Help --- Getting Warning!Nulls ignored in aggregate funcation

9. Using ACCESS 2000 to create SQL Server view and Store Procedures

10. Using DAO to create pass thru queries in access 2000 - references problem

11. Can not create view with access 2000 ADP to SQL 2000

12. SQL 2000 B2: Can't create views/diagrams from Access 2000 project

13. Vinculate Tables in Access 2000 using ADOX