can you intercept a SQL command before it is sent?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

The title probably doesn't make much sense so I'll attempt to clarify. I'm pretty knew to programming in Access and I'm trying to blend some of the automatic wizard stuff with hand coded parts

My problem is: I have a table that is linked to my postgresql database and when I finish typing a record into the table it sends it to the database as soon as I'm finished. This is good but I'd rather have it so that I can have Access not send any messages to the database until all the data in that table has been entered and not just one row of the data. I tried using the before update event but that doesn't block the SQL commands from being sent

Ideally, I'd like to get the command that Access was going to send to my database and be able to edit it and check it before it's sent and preferrably, I'd like to be able to have Access not send the message and instead send it myself

The only option I see is that I have to avoid using linked tables and instead just collect the info in my own tables and send the data myself. This would work but would probably double or triple the development time

Thanks for any help you guys can give me,
 
Hi,

The title probably doesn't make much sense so I'll attempt to clarify. I'm pretty knew to programming in Access and I'm trying to blend some of the automatic wizard stuff with hand coded parts.

My problem is: I have a table that is linked to my postgresql database and when I finish typing a record into the table it sends it to the database as soon as I'm finished. This is good but I'd rather have it so that I can have Access not send any messages to the database until all the data in that table has been entered and not just one row of the data. I tried using the before update event but that doesn't block the SQL commands from being sent.

Ideally, I'd like to get the command that Access was going to send to my database and be able to edit it and check it before it's sent and preferrably, I'd like to be able to have Access not send the message and instead send it myself.

The only option I see is that I have to avoid using linked tables and instead just collect the info in my own tables and send the data myself. This would work but would probably double or triple the development time.

Thanks for any help you guys can give me,
Click to expand...

Hi,

You're not going to prevent Access from updating each row in a linked
table. The moment you move to another row, the previous one is updated
in the database.

One way to accomplish it is to enter all your data into a local (Jet)
work table, then run some code to apply all the changes into the real
back-end table. You're right that this will mean more development time.

Also, I wouldn't work directly in the table. Instead, create a form
that will let you use all the events to manage the updating process. It
won't solve your original problem, but it will help if you go the work
table route.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
 
Well I was hoping that wouldn't be the answer but oh well

I used your advice with the local table idea. So far it is working

Thanks
 
Actually, Armen's answer isn't the only way.

You can create an unbound form, and write VBA code to take the values from a
recordset to populate the controls, then decide whether or not it's
necessary to issue an Update statement.
 
Actually, Armen's answer isn't the only way.

You can create an unbound form, and write VBA code to take the values from a
recordset to populate the controls, then decide whether or not it's
necessary to issue an Update statement.
Click to expand...

Yes, that's true. An unbound form can accomplish the same thing,
especially for one record at a time. However, the original question
seemed to involve multiple records on the form at the same time, which I
think would be easier using a local work table with a continuous form.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
 
You're right Armen, the local table idea is working very well

Thanks to both of you for the help, it is very much appreciated.
 
Back
Top