Get field list from SQL connected table 1

[thromada]

Hi all,
I have an Access 2002-2003 project (.adp) that uses the SQL datalink connection to an SQL database (not "linked tables"). The tables work OK and are listed in Access as "tablename(dbo)". I need a way to get a list of fields in one of these tables. I've tried DAO methods, but get an error at the db.TableDefs(table) command. It doesn't seem to behave the same if these were local or linked tables.

How can I get a list of the fields in one of these SQL connected tables? This is the code I tried:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(dbo.departments) <-- error here is "424 object required".

Thanks.

 

[PHV]

Perhaps this ?
Set tdf = db.TableDefs([!]"[/!]dbo.departments[!]"[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thromada]

I tried that and several other ways, and the error is "91 object variable or with block variable not set".

As I debug and step through, I notice that db, tdf, fld all have a value of "Nothing". It's like the way I'm trying to connect to these SQL connected tables isn't working the same as if they were linked or local tables.

Anybody know if connecting with the File | Connection | Datalink Properties has a way of listing the fields in one of those tables?

 

[PHV]

What about a ListBox with RecordSource="dbo.departments" and RowSourceType="Field list" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[thromada]

Great! That will do the job! Thanks PHV!