[AccessD] Querying user-defined system tables in Acc 2010-2013

[Dan Waters]

Perhaps this table is corrupt in some way? Recreate an empty Usys table that
duplicates the structure of the one you're working on.  Put in some data and
run your query.  Then import the original data to see if that also works.  

I once was unable to upsize an Access table to SQL using the Access upsizing
wizard.  I was able to finally find the problem using a program call MUST.
With that I found a single field in one row that had the wrong type of data
for that field (text in a date field or something ...).  I fixed that and
then was able to upsize successfully.

Dan

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Charlotte Foust
Sent: Monday, April 06, 2015 9:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Querying user-defined system tables in Acc 2010-2013

Right,  and what I expected.  Not.
On Apr 6, 2015 6:36 AM, "Heenan, Lambert" <Lambert.Heenan at aig.com> wrote:

> Share some code? :-)
>
> I just ran this code on one of my USYS tables and it behave correctly.
>
> Dim rs As DAO.Recordset
>     Set rs = Currentdb.OpenRecordset("SELECT USYS_LinkedTables_tbl.* 
> FROM USYS_LinkedTables_tbl;", dbOpenSnapshot)
>     rs.MoveLast
>     Debug.Print rs.RecordCount
>     rs.Close
>     Set rs = Nothing
>
> This is in Access 2010. Without rs.MoveLast the output is '1', but 
> that's normal too. :-)
>
> Lambert
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Charlotte Foust
> Sent: Sunday, April 05, 2015 10:00 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Querying user-defined system tables in Acc 
> 2010-2013
>
> I gave up and did a dlookup instead, which works just fine.  Go figure.
>
>
> Charlotte Foust
> (916) 206-4336
>
> On Sun, Apr 5, 2015 at 6:55 PM, Dan Waters <df.waters at outlook.com> wrote:
>
> > What about trying an ADO recordset instead of a DAO recordset?  (or
> > vice-versa)
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of Charlotte Foust
> > Sent: Sunday, April 05, 2015 8:38 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Querying user-defined system tables in Acc
> > 2010-2013
> >
> > That's what I tried first.  Same result.  I know back the mdb days 
> > you sometimes had to set the query to run with owner's permissions, 
> > but that went out with workgroup security.  I work with this stuff 
> > all the time, but I don't usually roll out a sort-of security system 
> > for clients, and that's what this is for.
> >
> > UD = "User-defined", a USys table.
> >
> >
> >
> > Charlotte Foust
> > (916) 206-4336
> >
> > On Sun, Apr 5, 2015 at 6:04 PM, Dan Waters <df.waters at outlook.com>
> wrote:
> >
> > > Just as a check, try using "SELECT * FROM [tablename]" instead of 
> > > your variable 'SQLstr'.
> > >
> > > Also, what is a UD System Table?
> > >
> > > Dan
> > >
> > > -----Original Message-----
> > > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > > Behalf Of Charlotte Foust
> > > Sent: Sunday, April 05, 2015 7:54 PM
> > > To: Access Developers discussion and problem solving
> > > Subject: [AccessD] Querying user-defined system tables in Acc
> > > 2010-2013
> > >
> > > I'm running into peculiar behavior where attempting to set a 
> > > recordset object from a select * query based on a UD system table 
> > > runs in the query design window but returns no records on 
> > > db.OpenRecordset(SQLstr,
> > > dbopensnapshot) in code.  Is it just another piece of my mind 
> > > drifting away, or what?
> > >
> > >
> > > Charlotte Foust
> > > (916) 206-4336
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com