Adding a field to an existing table in Access using VB

  • Thread starter Thread starter eroleyikan
  • Start date Start date
E

eroleyikan

I am using vb6 and access. I am trying to add a field to several
tables in the same database.

adoConn.Execute "Alter Table TestTable Add Column NewTextColumn3 Text"

I loop over table names and run this line for each of them. If I run
this code with a break in it and debug line by line I get no errors.

But if I run it without a break point, most tables work but one of
them (always the same) gives me an error saying that I "do not have
the necessary permissions to use the "xyz" object." Where xyz is my
table name. But then I click on debug, and continue executing line by
line, then it works!

I cannot solve the puzzle. I already spent several hours on this just
out of pure arrogance.

Does anyone know why this is happening, or how I might avoid the
error?

Thank you

Erol Eyikan

CT
 
Sometimes when you execute alot of DDL lines in a row, the
conection object completes before the table was actually
changed. This can lead to the error that you are seeing.

Putting in a short pause will usually fix the problem.


Chris
 
Thanks Chris, I added a 5 second delay after each execution. It is
working without the error. Since this is happening in a loop, I am
losing about a minute though. Is there a way to wait exactly as much
as necessary? Instead of specifying some miliseconds, can I say "wait
until it is done"? That would also make me feel more comforable to
know that the program will not fail on slower performing networks etc.
Thanks
Erol
 
This is somewhat similar to the problems with waiting for a Shell process to
complete. Maybe if you can get the Windows handle to the process, you can
use the WaitForSingleProcess API call to wait just long enough. Dont know
how you do this for a Connection process, but I expect there is a way.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top