Getting all fields for a certain table aps 22.02.2006 19:07:27 Hi, Is it possible to dynamically get a list of all the fields for a specified table? If so, how is it done. Many thanks in advance. Arthur.

Re: Getting all fields for a certain table "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 22.02.2006 20:42:18 Are you looking to do this via ADO, DAO, in a query, or something else I haven't thought of? Rob "aps" <aps[ at ]discussions.microsoft.com> wrote in message news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... [Quoted Text] > Hi, > > Is it possible to dynamically get a list of all the fields for a specified > table? If so, how is it done. > > Many thanks in advance. > Arthur. > >

Re: Getting all fields for a certain table aps 23.02.2006 09:25:27 Hi Rob, I connect using ADODB. Thanks. "Robert Morley" wrote: [Quoted Text] > Are you looking to do this via ADO, DAO, in a query, or something else I > haven't thought of? > > > > Rob > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > Hi, > > > > Is it possible to dynamically get a list of all the fields for a specified > > table? If so, how is it done. > > > > Many thanks in advance. > > Arthur. > > > > > > >

Re: Getting all fields for a certain table "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 23.02.2006 13:07:50 You can use the OpenSchema method. To get all of the columns in table Employees, you'd use something like: Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, "Employees", Null)) Do Until rstSchema.EOF Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME rstSchema.MoveNext Loop (where Cnxn is an already opened Connection to the database in question) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "aps" <aps[ at ]discussions.microsoft.com> wrote in message news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... [Quoted Text] > Hi Rob, > > I connect using ADODB. > > Thanks. > > "Robert Morley" wrote: > > > Are you looking to do this via ADO, DAO, in a query, or something else I > > haven't thought of? > > > > > > > > Rob > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > Hi, > > > > > > Is it possible to dynamically get a list of all the fields for a specified > > > table? If so, how is it done. > > > > > > Many thanks in advance. > > > Arthur. > > > > > > > > > > > >

Re: Getting all fields for a certain table aps 23.02.2006 14:21:26 Hi Doug, Thanks for the response. I have checked out the OpenSchema through the help, really useful. Trouble is that on opening the scema, there is no result, ie immediately I have EOF as true, therefore there is nothing to display. Tried changing to list of tables and had similar results. Even setting all criteria to NULL has a similar effect. Arthur. "Douglas J Steele" wrote: [Quoted Text] > You can use the OpenSchema method. > > To get all of the columns in table Employees, you'd use something like: > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > "Employees", Null)) > > Do Until rstSchema.EOF > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > rstSchema.MoveNext > Loop > > (where Cnxn is an already opened Connection to the database in question) > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > Hi Rob, > > > > I connect using ADODB. > > > > Thanks. > > > > "Robert Morley" wrote: > > > > > Are you looking to do this via ADO, DAO, in a query, or something else I > > > haven't thought of? > > > > > > > > > > > > Rob > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > Hi, > > > > > > > > Is it possible to dynamically get a list of all the fields for a > specified > > > > table? If so, how is it done. > > > > > > > > Many thanks in advance. > > > > Arthur. > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 23.02.2006 14:45:14 What's the exact code you're using? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "aps" <aps[ at ]discussions.microsoft.com> wrote in message news:25540F21-4BFF-4EFC-BE71-E4296214A383[ at ]microsoft.com... [Quoted Text] > Hi Doug, > > Thanks for the response. I have checked out the OpenSchema through the help, > really useful. Trouble is that on opening the scema, there is no result, ie > immediately I have EOF as true, therefore there is nothing to display. Tried > changing to list of tables and had similar results. Even setting all criteria > to NULL has a similar effect. > > Arthur. > > "Douglas J Steele" wrote: > > > You can use the OpenSchema method. > > > > To get all of the columns in table Employees, you'd use something like: > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > > "Employees", Null)) > > > > Do Until rstSchema.EOF > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > rstSchema.MoveNext > > Loop > > > > (where Cnxn is an already opened Connection to the database in question) > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no e-mails, please!) > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > Hi Rob, > > > > > > I connect using ADODB. > > > > > > Thanks. > > > > > > "Robert Morley" wrote: > > > > > > > Are you looking to do this via ADO, DAO, in a query, or something else I > > > > haven't thought of? > > > > > > > > > > > > > > > > Rob > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > Hi, > > > > > > > > > > Is it possible to dynamically get a list of all the fields for a > > specified > > > > > table? If so, how is it done. > > > > > > > > > > Many thanks in advance. > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table aps 23.02.2006 14:45:55 Hi Doug, Tried a few other things; The following works Set rstSchema = dbCon.OpenSchema(adSchemaColumns) but this one does not work Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, "Tender", Null)) where "Tender" is a valid table name. The tables are linked, so could the name be different from "Tender"? Thanks, Arthur. "Douglas J Steele" wrote: [Quoted Text] > You can use the OpenSchema method. > > To get all of the columns in table Employees, you'd use something like: > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > "Employees", Null)) > > Do Until rstSchema.EOF > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > rstSchema.MoveNext > Loop > > (where Cnxn is an already opened Connection to the database in question) > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > Hi Rob, > > > > I connect using ADODB. > > > > Thanks. > > > > "Robert Morley" wrote: > > > > > Are you looking to do this via ADO, DAO, in a query, or something else I > > > haven't thought of? > > > > > > > > > > > > Rob > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > Hi, > > > > > > > > Is it possible to dynamically get a list of all the fields for a > specified > > > > table? If so, how is it done. > > > > > > > > Many thanks in advance. > > > > Arthur. > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 23.02.2006 14:59:08 Just to find out, try running: Set rstSchema = Cnxn.OpenSchema(adSchemaColumns) Do Until rstSchema.EOF Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME rstSchema.MoveNext Loop and see what it thinks the table name is. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "aps" <aps[ at ]discussions.microsoft.com> wrote in message news:5D03C2D1-722E-47CF-B312-4F4806511AA3[ at ]microsoft.com... [Quoted Text] > Hi Doug, > > Tried a few other things; > > The following works > Set rstSchema = dbCon.OpenSchema(adSchemaColumns) > > but this one does not work > Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, > "Tender", Null)) > where "Tender" is a valid table name. > > The tables are linked, so could the name be different from "Tender"? > > Thanks, > Arthur. > > "Douglas J Steele" wrote: > > > You can use the OpenSchema method. > > > > To get all of the columns in table Employees, you'd use something like: > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > > "Employees", Null)) > > > > Do Until rstSchema.EOF > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > rstSchema.MoveNext > > Loop > > > > (where Cnxn is an already opened Connection to the database in question) > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no e-mails, please!) > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > Hi Rob, > > > > > > I connect using ADODB. > > > > > > Thanks. > > > > > > "Robert Morley" wrote: > > > > > > > Are you looking to do this via ADO, DAO, in a query, or something else I > > > > haven't thought of? > > > > > > > > > > > > > > > > Rob > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > Hi, > > > > > > > > > > Is it possible to dynamically get a list of all the fields for a > > specified > > > > > table? If so, how is it done. > > > > > > > > > > Many thanks in advance. > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table aps 23.02.2006 15:11:27 Hi Doug, Code is; Dim rstSchema As ADODB.Recordset Set dbCon = CreateObject("ADODB.Connection") Set dbCon = CurrentProject.Connection Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, "Tender", Null)) Do Until rstSchema.EOF Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME rstSchema.MoveNext Loop dbCon.Close Set rstSchema = Nothing Set dbCon = Nothing "Douglas J Steele" wrote: [Quoted Text] > What's the exact code you're using? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:25540F21-4BFF-4EFC-BE71-E4296214A383[ at ]microsoft.com... > > Hi Doug, > > > > Thanks for the response. I have checked out the OpenSchema through the > help, > > really useful. Trouble is that on opening the scema, there is no result, > ie > > immediately I have EOF as true, therefore there is nothing to display. > Tried > > changing to list of tables and had similar results. Even setting all > criteria > > to NULL has a similar effect. > > > > Arthur. > > > > "Douglas J Steele" wrote: > > > > > You can use the OpenSchema method. > > > > > > To get all of the columns in table Employees, you'd use something like: > > > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > > > "Employees", Null)) > > > > > > Do Until rstSchema.EOF > > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > > rstSchema.MoveNext > > > Loop > > > > > > (where Cnxn is an already opened Connection to the database in question) > > > > > > -- > > > Doug Steele, Microsoft Access MVP > > > http://I.Am/DougSteele > > > (no e-mails, please!) > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > > Hi Rob, > > > > > > > > I connect using ADODB. > > > > > > > > Thanks. > > > > > > > > "Robert Morley" wrote: > > > > > > > > > Are you looking to do this via ADO, DAO, in a query, or something > else I > > > > > haven't thought of? > > > > > > > > > > > > > > > > > > > > Rob > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > > Hi, > > > > > > > > > > > > Is it possible to dynamically get a list of all the fields for a > > > specified > > > > > > table? If so, how is it done. > > > > > > > > > > > > Many thanks in advance. > > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 23.02.2006 16:44:21 Did you try my advice to run without any condition Set rstSchema = Cnxn.OpenSchema(adSchemaColumns) and see what table name it uses? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "aps" <aps[ at ]discussions.microsoft.com> wrote in message news:6FB5346E-37A0-47C2-B6B8-EC7F1CAD2994[ at ]microsoft.com... [Quoted Text] > Hi Doug, > > Code is; > > Dim rstSchema As ADODB.Recordset > > Set dbCon = CreateObject("ADODB.Connection") > Set dbCon = CurrentProject.Connection > > Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, > "Tender", Null)) > > Do Until rstSchema.EOF > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > rstSchema.MoveNext > Loop > > dbCon.Close > Set rstSchema = Nothing > Set dbCon = Nothing > > > "Douglas J Steele" wrote: > > > What's the exact code you're using? > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no e-mails, please!) > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > news:25540F21-4BFF-4EFC-BE71-E4296214A383[ at ]microsoft.com... > > > Hi Doug, > > > > > > Thanks for the response. I have checked out the OpenSchema through the > > help, > > > really useful. Trouble is that on opening the scema, there is no result, > > ie > > > immediately I have EOF as true, therefore there is nothing to display. > > Tried > > > changing to list of tables and had similar results. Even setting all > > criteria > > > to NULL has a similar effect. > > > > > > Arthur. > > > > > > "Douglas J Steele" wrote: > > > > > > > You can use the OpenSchema method. > > > > > > > > To get all of the columns in table Employees, you'd use something like: > > > > > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, Null, > > > > "Employees", Null)) > > > > > > > > Do Until rstSchema.EOF > > > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > > > rstSchema.MoveNext > > > > Loop > > > > > > > > (where Cnxn is an already opened Connection to the database in question) > > > > > > > > -- > > > > Doug Steele, Microsoft Access MVP > > > > http://I.Am/DougSteele > > > > (no e-mails, please!) > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > > > Hi Rob, > > > > > > > > > > I connect using ADODB. > > > > > > > > > > Thanks. > > > > > > > > > > "Robert Morley" wrote: > > > > > > > > > > > Are you looking to do this via ADO, DAO, in a query, or something > > else I > > > > > > haven't thought of? > > > > > > > > > > > > > > > > > > > > > > > > Rob > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > > > Hi, > > > > > > > > > > > > > > Is it possible to dynamically get a list of all the fields for a > > > > specified > > > > > > > table? If so, how is it done. > > > > > > > > > > > > > > Many thanks in advance. > > > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table aps 23.02.2006 18:11:27 Hi Doug, Yes, I did that and the table display looks normal. I even ran the following If rstSchema!TABLE_NAME = "Tender" Then a = 1 End If with a break on the middle line. The app did indeed break. Looks like I will have to just test for the table I want like this and allow the inefficient processing of the other 100+ tables. Thanks for your help, at least I have an answer now. Regards, Arthur. "Douglas J Steele" wrote: [Quoted Text] > Did you try my advice to run without any condition > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns) > > and see what table name it uses? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:6FB5346E-37A0-47C2-B6B8-EC7F1CAD2994[ at ]microsoft.com... > > Hi Doug, > > > > Code is; > > > > Dim rstSchema As ADODB.Recordset > > > > Set dbCon = CreateObject("ADODB.Connection") > > Set dbCon = CurrentProject.Connection > > > > Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, > > "Tender", Null)) > > > > Do Until rstSchema.EOF > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > rstSchema.MoveNext > > Loop > > > > dbCon.Close > > Set rstSchema = Nothing > > Set dbCon = Nothing > > > > > > "Douglas J Steele" wrote: > > > > > What's the exact code you're using? > > > > > > -- > > > Doug Steele, Microsoft Access MVP > > > http://I.Am/DougSteele > > > (no e-mails, please!) > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > news:25540F21-4BFF-4EFC-BE71-E4296214A383[ at ]microsoft.com... > > > > Hi Doug, > > > > > > > > Thanks for the response. I have checked out the OpenSchema through the > > > help, > > > > really useful. Trouble is that on opening the scema, there is no > result, > > > ie > > > > immediately I have EOF as true, therefore there is nothing to display. > > > Tried > > > > changing to list of tables and had similar results. Even setting all > > > criteria > > > > to NULL has a similar effect. > > > > > > > > Arthur. > > > > > > > > "Douglas J Steele" wrote: > > > > > > > > > You can use the OpenSchema method. > > > > > > > > > > To get all of the columns in table Employees, you'd use something > like: > > > > > > > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, > Null, > > > > > "Employees", Null)) > > > > > > > > > > Do Until rstSchema.EOF > > > > > Debug.Print rstSchema!TABLE_NAME & ": " & > rstSchema!COLUMN_NAME > > > > > rstSchema.MoveNext > > > > > Loop > > > > > > > > > > (where Cnxn is an already opened Connection to the database in > question) > > > > > > > > > > -- > > > > > Doug Steele, Microsoft Access MVP > > > > > http://I.Am/DougSteele > > > > > (no e-mails, please!) > > > > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > > > > Hi Rob, > > > > > > > > > > > > I connect using ADODB. > > > > > > > > > > > > Thanks. > > > > > > > > > > > > "Robert Morley" wrote: > > > > > > > > > > > > > Are you looking to do this via ADO, DAO, in a query, or > something > > > else I > > > > > > > haven't thought of? > > > > > > > > > > > > > > > > > > > > > > > > > > > > Rob > > > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > > > > Hi, > > > > > > > > > > > > > > > > Is it possible to dynamically get a list of all the fields for > a > > > > > specified > > > > > > > > table? If so, how is it done. > > > > > > > > > > > > > > > > Many thanks in advance. > > > > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

Re: Getting all fields for a certain table aps 23.02.2006 18:18:33 Hi Doug, I have found how to make it work, instead of the following Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, "Tender", Null)) I tried; Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Tender", Empty)) And it works beautifully. Thanks, Arthur. "Douglas J Steele" wrote: [Quoted Text] > Did you try my advice to run without any condition > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns) > > and see what table name it uses? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > news:6FB5346E-37A0-47C2-B6B8-EC7F1CAD2994[ at ]microsoft.com... > > Hi Doug, > > > > Code is; > > > > Dim rstSchema As ADODB.Recordset > > > > Set dbCon = CreateObject("ADODB.Connection") > > Set dbCon = CurrentProject.Connection > > > > Set rstSchema = dbCon.OpenSchema(adSchemaColumns, Array(Null, Null, > > "Tender", Null)) > > > > Do Until rstSchema.EOF > > Debug.Print rstSchema!TABLE_NAME & ": " & rstSchema!COLUMN_NAME > > rstSchema.MoveNext > > Loop > > > > dbCon.Close > > Set rstSchema = Nothing > > Set dbCon = Nothing > > > > > > "Douglas J Steele" wrote: > > > > > What's the exact code you're using? > > > > > > -- > > > Doug Steele, Microsoft Access MVP > > > http://I.Am/DougSteele > > > (no e-mails, please!) > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > news:25540F21-4BFF-4EFC-BE71-E4296214A383[ at ]microsoft.com... > > > > Hi Doug, > > > > > > > > Thanks for the response. I have checked out the OpenSchema through the > > > help, > > > > really useful. Trouble is that on opening the scema, there is no > result, > > > ie > > > > immediately I have EOF as true, therefore there is nothing to display. > > > Tried > > > > changing to list of tables and had similar results. Even setting all > > > criteria > > > > to NULL has a similar effect. > > > > > > > > Arthur. > > > > > > > > "Douglas J Steele" wrote: > > > > > > > > > You can use the OpenSchema method. > > > > > > > > > > To get all of the columns in table Employees, you'd use something > like: > > > > > > > > > > Set rstSchema = Cnxn.OpenSchema(adSchemaColumns, Array(Null, > Null, > > > > > "Employees", Null)) > > > > > > > > > > Do Until rstSchema.EOF > > > > > Debug.Print rstSchema!TABLE_NAME & ": " & > rstSchema!COLUMN_NAME > > > > > rstSchema.MoveNext > > > > > Loop > > > > > > > > > > (where Cnxn is an already opened Connection to the database in > question) > > > > > > > > > > -- > > > > > Doug Steele, Microsoft Access MVP > > > > > http://I.Am/DougSteele > > > > > (no e-mails, please!) > > > > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > news:FD117EF8-FC82-484D-9DD4-C252CF81CD31[ at ]microsoft.com... > > > > > > Hi Rob, > > > > > > > > > > > > I connect using ADODB. > > > > > > > > > > > > Thanks. > > > > > > > > > > > > "Robert Morley" wrote: > > > > > > > > > > > > > Are you looking to do this via ADO, DAO, in a query, or > something > > > else I > > > > > > > haven't thought of? > > > > > > > > > > > > > > > > > > > > > > > > > > > > Rob > > > > > > > > > > > > > > "aps" <aps[ at ]discussions.microsoft.com> wrote in message > > > > > > > news:7EACBEC1-AE96-46CC-A179-EB2C4510D19B[ at ]microsoft.com... > > > > > > > > Hi, > > > > > > > > > > > > > > > > Is it possible to dynamically get a list of all the fields for > a > > > > > specified > > > > > > > > table? If so, how is it done. > > > > > > > > > > > > > > > > Many thanks in advance. > > > > > > > > Arthur. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >

RE: Getting all fields for a certain table DavidAtCaspian 27.03.2006 09:16:02 Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(Recordset) Dim fld As DAO.Field For Each fld In rs.Fields MsgBox fld.Name Next fld This works for DAO, and should work OK for ADO. You could substitute the msgbox line for an out put to a table with the fields "RecordsetName" and "FieldName", and then read your 'fields' table If you make this a sub receiving a recordset object, it could be put into a utilitie module and called from anywhere. like this Sub FLDNames(RS As DAO.Recordset) Dim fld As DAO.Field For Each fld In RS.Fields MsgBox fld.Name Next fld End Sub Any good??? "aps" wrote: [Quoted Text] > Hi, > > Is it possible to dynamically get a list of all the fields for a specified > table? If so, how is it done. > > Many thanks in advance. > Arthur. > >

RE: Getting all fields for a certain table aps 27.03.2006 09:24:02 Hi David, Thanks for your suggestion, looks really useful. "DavidAtCaspian" wrote: [Quoted Text] > Dim rs As DAO.Recordset > > Set rs = CurrentDb.OpenRecordset(Recordset) > Dim fld As DAO.Field > > For Each fld In rs.Fields > > MsgBox fld.Name > > Next fld > > > This works for DAO, and should work OK for ADO. > You could substitute the msgbox line for an out put to a table with the > fields "RecordsetName" and "FieldName", and then read your 'fields' table > > If you make this a sub receiving a recordset object, it could be put into a > utilitie module and called from anywhere. > > like this > > Sub FLDNames(RS As DAO.Recordset) > > Dim fld As DAO.Field > > For Each fld In RS.Fields > > MsgBox fld.Name > > Next fld > > End Sub > > > Any good??? > > > "aps" wrote: > > > Hi, > > > > Is it possible to dynamically get a list of all the fields for a specified > > table? If so, how is it done. > > > > Many thanks in advance. > > Arthur. > > > >

RE: Getting all fields for a certain table DavidAtCaspian 28.03.2006 12:26:02 Just found something that might be even better. Using a scripting.dictionary object, you can not only get all the field names, but also store and retrieve their values. Or you can try an array, or a collection, depending on how you want to retrieve them afterwards. Have a quick look at at the thread dealing with an equivalent to the Foxpro Scatter/Gather on this board. Brendan has sent an idea and I've posted both of my subs. (Not fully error trapped yet, but basically functional). David "aps" wrote: [Quoted Text] > Hi David, > > Thanks for your suggestion, looks really useful. > > "DavidAtCaspian" wrote: > > > Dim rs As DAO.Recordset > > > > Set rs = CurrentDb.OpenRecordset(Recordset) > > Dim fld As DAO.Field > > > > For Each fld In rs.Fields > > > > MsgBox fld.Name > > > > Next fld > > > > > > This works for DAO, and should work OK for ADO. > > You could substitute the msgbox line for an out put to a table with the > > fields "RecordsetName" and "FieldName", and then read your 'fields' table > > > > If you make this a sub receiving a recordset object, it could be put into a > > utilitie module and called from anywhere. > > > > like this > > > > Sub FLDNames(RS As DAO.Recordset) > > > > Dim fld As DAO.Field > > > > For Each fld In RS.Fields > > > > MsgBox fld.Name > > > > Next fld > > > > End Sub > > > > > > Any good??? > > > > > > "aps" wrote: > > > > > Hi, > > > > > > Is it possible to dynamically get a list of all the fields for a specified > > > table? If so, how is it done. > > > > > > Many thanks in advance. > > > Arthur. > > > > > >