0

How can I extract the table name from a field in an open ADO recordset? I can use the .source property to return the select statement, but if I want to know which table a field is associated with, how would I do that?

i.e. if .source returns a something like SELECT Foo, Boo, Shoo FROM table1, table2 how could I query the recordset to see which table Boo is coming from?

My data source is a SQL Server 2005 database and I'm using VBA 7.0/VBA 6.0 (runs in both)

CBRF23
  • 1,340
  • 1
  • 16
  • 44
  • 1
    there is no direct way as the recordset only represents the result of the query without knowing the secrets of the backend. If you want to now from which table which field comes you first have to parse the SQL from .source. If you have a table name or a shortcut in front of a field name you will know the table. If you don't have, the only way ist to retrieve an additonal recordset for each table and see if the field is in there. More complicated would be if the source is a view on the server and you want to know the original table. – cboden Nov 04 '14 at 15:44
  • So in the above example, I would need to create a function to extract each field name and each table name from the `.source` return and then loop through each and create a recordset using something like `"SELECT TOP 1 * FROM " & TABLENAME` and then loop through the field names looking for a match. Ugh. That seems really clunky but I can do it. I guess I was just hoping for a more elegant solution :/ – CBRF23 Nov 04 '14 at 15:50
  • 1
    yeah ... didn't see a smarter solution. But again: this would also not work if the server uses a view. You will have no chance to determine the underlying tables then. – cboden Nov 04 '14 at 16:08
  • Good point! For the project I'm working on now, that isn't a concern, but for future projects that certainly could be. – CBRF23 Nov 04 '14 at 17:11
  • 1
    only possible solution for views would be if you have access to the system views. there is one with all the view definitions in. But that can also be hard stuff as a view can contain other views and functions for example. So it's possible in general but a lot of effort. Good, that this isn't an issue for now ;-) by the way: if you have access to the system views you can also retrieve the table columns from there ... so it would requiere only one great retrieve with all tables and columns instead of several Select Top 1 – cboden Nov 04 '14 at 17:34
  • That is an interesting thought. I might play with it when I have some down time, but no time to do so now. I've got everything working for this project. It's a little clunkier than I like, but it executes quick enough. Definitely want to play with some alternative methods when I have time though. – CBRF23 Nov 04 '14 at 18:59
  • We are spending a lot of time wondering if we could, but it would be better to wonder if we should. – le3th4x0rbot Nov 18 '14 at 11:14
  • If you have an alternative, or a constructive solution to the original problem (trying to determine what table a field belongs to) I would love to hear it. Or even if you could elaborate on why one shouldn't attempt anything like this, as your comment insinuates, I would be interested to hear it. – CBRF23 Nov 18 '14 at 13:10

0 Answers0