Link to home
Start Free TrialLog in
Avatar of cogc_it
cogc_it

asked on

Code an Update in Access VBA

I have the task of updating some Null values in some fields in an Access database. The Null Fields need to be updated with the prior row's value for that field.
I am very competent in TSQL and had gone about creating a TSQL script that did just that after I imported the table into SQL Server.
What I learned later is that what my boss is requesting is a routine in Access VBA or VB that does the same thing. He does not want me to do it in TSQL.
I can provide a sample of the Access database and My TSQL script as an example of what I need to to do. But th ebottom line is I don't quite know how to do it in VBA and I need help.

The fields with NULL values that need to be updated with the prior NON NULL row's value are Title, Pay Rate, Annualized.
*Note that in myattached script I had only done Title and while in my script I was doing an insert, this needs to be an update.

Thanks
Update-Employee-Title2.sql
EmployeeReview---Copy.accdb
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

maybe you should tell your boss that it is absolutely counterproductive to achieve something in VBA which was already coded in T-SQL. It should be clear that T-SQL is absolutely faster than any VBA code.
If the table is not available in Access for direct access then it would also be better to translate the T-SQL script into some queries with Access SQL instead of looping through recordsets - that should always be the last choice if all other variants are impossible.
If the server IS available then create a stored procedure or view and access this from Access, if you want, with VBA.

As I always say: Think SQL and not VBA. A database is made to be queries with SQL and not with VBA.

Cheers,

Christian
Avatar of cogc_it
cogc_it

ASKER

Christian, I couldn't agree with you more. I don't always have the last say.