Changing the tables column size using VBA

[timhysniu]

Hello group,

I am currently working on a project on MS Access using forms and VBA. What I'm doing at the moment is:
- creating a new query using CurrentDB.CreateNewQueryDef
- changing it's SQL using my form values as parameters
- changing the subform's SourceObject to "CreatedQuery"
- deleting the query

So I have subformX and qr (As DAO.QueryDef). Whenever I execute this procedure I get a new table and the table column sizes are always a bit small. How can I programmatically change these column sizes? Any suggestions?

Thank You so much
tim

 

[ghudson]

Not possible. But why would you need to change the size [width] of a field [column] in a table? The users are not supposed to have access to view or edit a table [or any db object]. You can have the data displayed in a form or subform in a datasheet view and that will not change. I also advise you to delete the contents of the table instead of making a new table every time you run your query.
Then all you have to do is run an append query to update the table.

 

[timhysniu]

thanks for the reply.

Thanks very much for the reply. At least now I know its not possible.

Pardon for my lack of access terminology. When I said table I actually meant datasheet. This datasheet has the newly created query as a source.

You are right. If I didn't delete the query everytime, perhaps the widths of columns could be manually set and they wouldn't change anymore.

However, the reason I delete the query everytime is because everytime I update its SQL (DAO.QueryDef.SQL property) I get a corrupt query that no longer executes. In fact, I can't even edit it anymore to see where the fault is. (I believe the error was something about 'no table reference in a query' or something like that). No clue why this happens, but deleting the query and then recreating it works at least.

Any comments welcome,

tim