Hi All,
I've inherited an Excel app that uses an MS Access db back-end to store data. (We have multiple users for this app, each with their own data in their DB files.) My predecessor set some fields in some tables to use the Lookup feature and one table 's field has a rowsource that's no longer valid, i.e., he hard-coded some values in the lookup's query that are now obsolete. So I want to remove the rowsource setting. Ugh! This is why I *never* set field lookups in tables, and I bet most developers out there agree.
I've created an Excel "DB UPDATER" file that I'm distributing to all the users. It will connect to their DB file and make several other updates automatically for them, such as adding fields to some tables, deleting old tables, etc. I'd like to add code that also removes this lookup from each user's table. But I can't find any SQL "Alter Table" code that will do this. I tried the following w/o success.
szSQL = "ALTER TABLE [tblDATA1] " & _
"ALTER COLUMN [Data_Type] DROP ROWSOURCE"
I also guessed at some alternatives (ROWSOURCE = "") but still no luck.
I know how to delete the rowsource manually but our db file is PW protected so users can't get into their own files, themselves (without me giving them the PW), to make the change manually. My PLAN B will be to write code that re-creates the tables not having these lookups and copy the date into the new tables. But that approach is much less desirable because I'll also have to delete and recreate joins for the table, etc. Possible... but harder.
Anyone out there know how I can remove the lookup setting in the table's field(s) using VBA from either Excel or Access?
Thanks in advance...
I've inherited an Excel app that uses an MS Access db back-end to store data. (We have multiple users for this app, each with their own data in their DB files.) My predecessor set some fields in some tables to use the Lookup feature and one table 's field has a rowsource that's no longer valid, i.e., he hard-coded some values in the lookup's query that are now obsolete. So I want to remove the rowsource setting. Ugh! This is why I *never* set field lookups in tables, and I bet most developers out there agree.
I've created an Excel "DB UPDATER" file that I'm distributing to all the users. It will connect to their DB file and make several other updates automatically for them, such as adding fields to some tables, deleting old tables, etc. I'd like to add code that also removes this lookup from each user's table. But I can't find any SQL "Alter Table" code that will do this. I tried the following w/o success.
szSQL = "ALTER TABLE [tblDATA1] " & _
"ALTER COLUMN [Data_Type] DROP ROWSOURCE"
I also guessed at some alternatives (ROWSOURCE = "") but still no luck.
I know how to delete the rowsource manually but our db file is PW protected so users can't get into their own files, themselves (without me giving them the PW), to make the change manually. My PLAN B will be to write code that re-creates the tables not having these lookups and copy the date into the new tables. But that approach is much less desirable because I'll also have to delete and recreate joins for the table, etc. Possible... but harder.
Anyone out there know how I can remove the lookup setting in the table's field(s) using VBA from either Excel or Access?
Thanks in advance...
