prorgrammatically retreive Row Source SQL for Lookup columns?

[lex3001]

I'd like to write a program to open up several MDBs, retrieve the list of
tables and columns/fields for each MDB, and also retrieve the SQL used for
the Row Source for any of the columns which are designated as Lookup columns.

Is this possible? How?

I can't seem to find the SQL queries used in the Row Source anywhere in the
Msys* tables (can't even find a list of columns in there) and the Access
Object Model also does not appear to have information on columns (no
fields/columns collection in the objects returned by AllTables).

 

[Paul Shapiro]

This is a bit lengthy, but here's some code to retrieve lookup info from a
DAO field object properties. The code was written for checking students'
Access projects, so it includes text warning of what I consider unacceptable
settings. The fld object comes from the relationships, so the fields being
accessed here are all FK's.

If Len(fld.Properties("RowSource")) = 0 Then
'No lookup properties
ElseIf fld.Properties("RowSourceType") = "Table/Query" Then
'Does the rowsource text exist as a table name?
If Len(dbData.TableDefs(fld.Properties("RowSource")).Name) = 0 Then
'Row source is NOT a table name. Is the row source sorted?
strRowSource = fld.Properties("RowSource")
'Is it a saved query?
If Len(dbData.QueryDefs(strRowSource).Name) = 0 Then
'Nothing to do. We already got the SQL.
Else
'Row source is a saved query. Get the SQL.
strRowSource = dbData.QueryDefs(strRowSource).SQL
End If

If Not strRowSource Like "*Order By *" Then
'No sorting specified
strFieldProblemTemp = strFieldProblemTemp _
& "FK lookup row source needs to be sorted. "
End If
ElseIf Len(dbData.QueryDefs(fld.Properties("RowSource")).Name) = 0 Then
'Row source is just a table name
strFieldProblemTemp = strFieldProblemTemp _
& "FK lookup row source (" _
& fld.Properties("RowSource") _
& ") is just a table name. " _
& "It must be a query specifying columns and sorting. "
Else 'This code shouldn't ever execute, since querydefs do not show
up in tabledefs.
'But I added the code because sometimes they did????
'Row source is a query name
strRowSource = dbData.QueryDefs(strRowSource).SQL
If Not strRowSource Like "*Order By *" Then
'No sorting specified
strFieldProblemTemp = strFieldProblemTemp _
& "FK lookup row source needs to be sorted. "
End If
End If

 

[aaron.kempf]

uh, you're crazy

I personally just keep everything in SQL Server-- it's an engine with
a future-- it makes a big difference in the big scheme of things.

Do you really _TEACH_ kids to use Access (Jet)?

What a worthless ****nig class, get off your ass and learn a real
database!

-Aaron

 

[aaron.kempf]

good for you.

Maybe you should go and learn a practical database.

Jet is obsolete and it has been for a decade.
People that use Jet-- are the same kinda dipshits that build security
holes throughout their system.

Obsolete-- and it has been for a decade.

-Aaron

 

[lex3001]

Thanks Paul -- this is exactly what I needed. It hadn't occurred to me to
look at the DAO object model, and even if it had, I wouldn't have guessed to
look for the RowSource in the Properties collection (especially since
attempting to look at it for other fields would result in an error that it is
not in the collection).

I was able to easily adapt this to .NET (which my utility is written in) by
importing a COM reference to DAO 3.6:

DirectoryInfo di = new DirectoryInfo(@"C:\AccessFiles");
foreach (FileInfo fi in di.GetFiles("*.mdb"))
{
dao.DBEngine dbE;
dao.Database db;
string strCon;

dbE = new dao.DBEngine();
strCon = fi.FullName;
db = dbE.OpenDatabase(strCon, false, true, string.Empty);
foreach (dao.TableDef tbl in db.TableDefs)
{
Console.WriteLine("\n{0}\n==============", tbl.Name);
foreach (dao.Field fld in tbl.Fields)
{
string rowSouceType = null;
string rowSouce = null;
try
{
rowSouceType = fld.Properties["RowSourceType"].Value.ToString();
rowSouce = fld.Properties["RowSource"].Value.ToString();
}
catch { }
Console.WriteLine("\t{0}\t{1}\t{2}", fld.Name, rowSouceType,
rowSouce);
}
}

 

[Paul Shapiro]

That code was a small piece of a module which prepares a fairly
comprehensive mdb report, including data structure, queries, forms and
reports. The output is generated as either plain text or a formatted Word
document. You can email me (make the obvious correction to the listed email)
if you want a copy of the complete module.

 

[aaron.kempf]

there's no such thing as a comprehensive mdb report.

there's just mountains and mountains of unmaintainable vba script.

Jet ****ing sucks.
Upsize to SQL Server-- take a class if you need to.

But talk about wasting time-- on an obsolete platform!!!

Jet has been obsolete for a decade!!!
Just because some fat lazy canadian-- doesn't have the mental capacity
to successfully upsize the _ONE_ application that he has built-- does
that mean that it's too hard for you and me?

SQL Server is _EASIER_ than jet.
Because SHIT JUST WORKS and it works right the first try.

-Aaron

That code was a small piece of a module which prepares a fairly
comprehensive mdb report, including data structure, queries, forms and
reports. The output is generated as either plain text or a formatted Word
document. You can email me (make the obvious correction to the listed email)
if you want a copy of the complete module.

Thanks Paul -- this is exactly what I needed. It hadn't occurred to me to
look at the DAO object model, and even if it had, I wouldn't have guessed
to
look for the RowSource in the Properties collection (especially since
attempting to look at it for other fields would result in an error thatit
is
not in the collection).

I was able to easily adapt this to .NET (which my utility is written in)
by
importing a COM reference to DAO 3.6:

     DirectoryInfo di = new DirectoryInfo(@"C:\AccessFiles");
     foreach (FileInfo fi in di.GetFiles("*.mdb"))
     {
       dao.DBEngine dbE;
       dao.Database db;
       string strCon;

       dbE = new dao.DBEngine();
       strCon = fi.FullName;
       db = dbE.OpenDatabase(strCon, false, true, string.Empty);
       foreach (dao.TableDef tbl in db.TableDefs)
       {
         Console.WriteLine("\n{0}\n==============", tbl.Name);
         foreach (dao.Field fld in tbl.Fields)
         {
           string rowSouceType = null;
           string rowSouce = null;
           try
           {
             rowSouceType =
fld.Properties["RowSourceType"].Value.ToString();
             rowSouce = fld.Properties["RowSource"].Value.ToString();
           }
           catch { }
           Console.WriteLine("\t{0}\t{1}\t{2}", fld.Name, rowSouceType,
rowSouce);
         }
       }