convert query into the VB code

[Guest]

Hey,

I have db update process all in queries, but I need to have it as a code
procedures.
Have little knowledge in code writing. I don't know if it's possible and I
would appreciate any help with it.
Here is SQL of the queries that i need to have as codes:
1. UPDATE Cyc17 SET Cyc17.[Date] = LastWorkDay(DateSerial(Year([Enter any
date in the month being updated in format: mm/dd/yyyy]),Month([Enter any date
in the month being updated in format: mm/dd/yyyy])+1,0));
2. INSERT INTO Cyc17_agr ( [Date], Del, units, amounts )
SELECT Cyc17.Date, Cyc17.del, Cyc17.units, Cyc17.amounts
FROM Cyc17;
3. DELETE Cyc17_agr.Del
FROM Cyc17_agr
WHERE (((Cyc17_agr.Del) Is Null));
4. UPDATE Cyc17_agr SET Cyc17_agr.amounts = 0
WHERE (((Cyc17_agr.amounts) Is Null));

LastWorkDay is the function that returns the last working day of the month.
I thought may be I should try to create 4 different procedures and then call
them from the main procedure. Or maybe it's possible to have all of it in the
one function. I don't know. Waiting for someone's help. Thank you. Juli.

 

[Guest]

Juli,

Why do you need it in code procedures?
And, to what level do you need it in code? You can execute an SQL statment
from VBA or, you can use Recordset Processing which doesn't use SQL at all.
In almost all cases, SQL will be faster and less work for you.

If you can explain the why, I think we can help determine the best way to do
this.

 

[Guest]

In the database for different portfolio I do it through queries and it works
perfect. For this database I need to update at least 15 different tables this
way. I thought, If I have 4 update queries for each table, (plus few select
queries for each table) my database would be huge right away, even without
data. I don't really care either it SQL within VB or Recordset procedure.
meanwhile i tried to create DAO procedure for the Q1. It doesn't show any
error but doesn't update the table.
It's obviously wrong, but here it is:


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("cyc17")

With rs
.Edit
![date] = lastWorkDay(1 / 1 / 6)
End With
rs.Close

I would be greatfull for any kind of help. Thanks

Juli,

Why do you need it in code procedures?
And, to what level do you need it in code? You can execute an SQL statment
from VBA or, you can use Recordset Processing which doesn't use SQL at all.
In almost all cases, SQL will be faster and less work for you.

If you can explain the why, I think we can help determine the best way to do
this.
--
Dave Hargis, Microsoft Access MVP

Hey,

I have db update process all in queries, but I need to have it as a code
procedures.
Have little knowledge in code writing. I don't know if it's possible and I
would appreciate any help with it.
Here is SQL of the queries that i need to have as codes:
1. UPDATE Cyc17 SET Cyc17.[Date] = LastWorkDay(DateSerial(Year([Enter any
date in the month being updated in format: mm/dd/yyyy]),Month([Enter any date
in the month being updated in format: mm/dd/yyyy])+1,0));
2. INSERT INTO Cyc17_agr ( [Date], Del, units, amounts )
SELECT Cyc17.Date, Cyc17.del, Cyc17.units, Cyc17.amounts
FROM Cyc17;
3. DELETE Cyc17_agr.Del
FROM Cyc17_agr
WHERE (((Cyc17_agr.Del) Is Null));
4. UPDATE Cyc17_agr SET Cyc17_agr.amounts = 0
WHERE (((Cyc17_agr.amounts) Is Null));

LastWorkDay is the function that returns the last working day of the month.
I thought may be I should try to create 4 different procedures and then call
them from the main procedure. Or maybe it's possible to have all of it in the
one function. I don't know. Waiting for someone's help. Thank you. Juli.

 

[Guest]

You left out the update part:
With rs
.Edit
![date] = lastWorkDay(1 / 1 / 6)
.Update
.Close
End With
Set rs = Nothing

(Note changes)

Now, you database size issue really is a non issue. I have applications
with several hundred queries of various sorts. So don't worry about that.
I would suggest you just create a function that executes all the queries.
Since they are action queries, I recommend the Execute method rather than the
RunSQL method. First, it is much faster because it does not go through the
Access User Interface - It goest directly to Jet. In addition, this allows
you to avoid the warning messages without having to setwarning on or off.

CurrentDb.Execute("MyActionQueryName"), dbFailOnError

It is important to include the dbFailOnError so if an error occurs, you will
know about it; otherwise, an error will occur without you knowing.

One other issue regarding size that you mentioned that make me a bit nervous:
my database would be huge right away, even without data

Hopefully, you will not have your data in the same mdb as your application.
It is very important to always split your database so you have a front end
that contains all your forms, reports, queries, macros, and modules and a
back end that contains all your tables, indexes, and relationships.

There are too many reasons to do this to go into, but trust me, it is the
correct thing to do.

--
Dave Hargis, Microsoft Access MVP

In the database for different portfolio I do it through queries and it works
perfect. For this database I need to update at least 15 different tables this
way. I thought, If I have 4 update queries for each table, (plus few select
queries for each table) my database would be huge right away, even without
data. I don't really care either it SQL within VB or Recordset procedure.
meanwhile i tried to create DAO procedure for the Q1. It doesn't show any
error but doesn't update the table.
It's obviously wrong, but here it is:


Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("cyc17")

With rs
.Edit
![date] = lastWorkDay(1 / 1 / 6)
End With
rs.Close

I would be greatfull for any kind of help. Thanks

Juli,

Why do you need it in code procedures?
And, to what level do you need it in code? You can execute an SQL statment
from VBA or, you can use Recordset Processing which doesn't use SQL at all.
In almost all cases, SQL will be faster and less work for you.

If you can explain the why, I think we can help determine the best way to do
this.
--
Dave Hargis, Microsoft Access MVP

Hey,

I have db update process all in queries, but I need to have it as a code
procedures.
Have little knowledge in code writing. I don't know if it's possible and I
would appreciate any help with it.
Here is SQL of the queries that i need to have as codes:
1. UPDATE Cyc17 SET Cyc17.[Date] = LastWorkDay(DateSerial(Year([Enter any
date in the month being updated in format: mm/dd/yyyy]),Month([Enter any date
in the month being updated in format: mm/dd/yyyy])+1,0));
2. INSERT INTO Cyc17_agr ( [Date], Del, units, amounts )
SELECT Cyc17.Date, Cyc17.del, Cyc17.units, Cyc17.amounts
FROM Cyc17;
3. DELETE Cyc17_agr.Del
FROM Cyc17_agr
WHERE (((Cyc17_agr.Del) Is Null));
4. UPDATE Cyc17_agr SET Cyc17_agr.amounts = 0
WHERE (((Cyc17_agr.amounts) Is Null));

LastWorkDay is the function that returns the last working day of the month.
I thought may be I should try to create 4 different procedures and then call
them from the main procedure. Or maybe it's possible to have all of it in the
one function. I don't know. Waiting for someone's help. Thank you. Juli.