Question Booking system using Access VBA (1 Viewer)

[varunvithalani]

Hello,

I have been given a task to develop a booking system with Microsoft Access VBA for my project.

Project Details:
To create a graphical user interface database which would show, add, delete, edit details of the customers, companies, orders, products, staff, departments of an engineering company.

Problems:
I am very familiar with Visual Basic but lack knowledge of Microsoft access. I have a few key tasks which I am listing below with its possible solutions. Kindly correct me or provide an alternative wherever possible.
1) The project requires to use minimal amount of memory and automatically delete old entries from the database after keeping a back up:
In this case, I decided to make use of the Compact and Repair feature of MS Access to reduce memory usage. But I do not understand how I would be able to use VB to delete entries automatically? (say entries older than 6 months) OR do I need to use SQL? (which I have no clue about).
Also, How can I transfer data from MS Access to an excel file to creat backup automatically after a period of time? (through a piece of code and NOT manually)

2) I have a date field in my one of my tables and I want my program to generate the next 4 months date automatically without the user manually typing it in. How can I program that?

3) The project also asks me to create a button which would call a macro or form which would allow me to key in all the public holidays of the year. Upon entering these holidays, my program should automatically be able to set the word HOLIDAY in one of the columns of the row of that particular date in a table.
I am really confused whether VB can do this or SQL must be implemented in VB?


Kindly help me on these issues if you have inputs or sample codes.
I will be really grateful. I am a little new to access, but hopefully with your help , will catch up.

Thanks,
Varun Vithalani

 

[DCrake]

For a start, do not make duplicate posts. Secondly when is the assignment due? This seems like schoolwork to me.

 

[varunvithalani]

Hello,

I sincerely apologize for duplicating posts. I just posted in the categories where it was related to.

It may sound like a very amateur level project to you, but I am new to Access VBA and thus would appreciate some inputs.

For your question, the project is due in 24 days time and thus I need to start working on it.

Thank you.

 

[acarter1]

Well you could use a query to find the records that you wish to back up and delete. Then base 2 queries on this a delete query and a append query. Then make a macro to run the append query to back up the data to another table and then delete them from the previous table.

As for moving data to a spreadsheet you can use a query again to find the records you want to move and then use a macro using the TransferSpreadsheet property to move the data from the query to a spreadsheet.

For the bank holiday form, link a form to a bank holiday table and set it to continuous form so that all records are showed and you can then enter bank holidays, create a button that on the click runs an update query to compare the two dates from the tables and put a yes in a holiday column on the table and put a no where it does not match.

 

[varunvithalani]

Thanks for your reply,

I vaguely understood the part about the query, but I had a doubt about the spreadsheet thing. The macro will be called in the VB code isn't it? If I want to create back up at all times, how do I perform the macro on every single entry. Should I do it when the user confirms all data entries? I guess I answered myself.

And on the other part,
How is it possible for the table to auto generate 4 months of date columns in advance?

Thanks a lot.

 

[acarter1]

Right so design a query so when you run it, it only shows the records you want to back up. And then design a macro to use TransferSpreadsheet to the name of your spreadsheet and location etc. You can then run this macro via a button on a form or another event using vb code. There is a button wizard and you can select run a macro as a generic button. You could make the macro so that after it transfers the data and then runs a delete query to delete the data from the table that you have backed up. The macro will only be run on the records in the query you make. You should then put a button on the form to run the macro.

Not too sure about the adding of new dates to table, some kind of loop needs to be made to increment the value of 01/01/2010 by 1 and then add this value to the table until the correct dates are all in the table maybe by using the Max function to determine the highest value already in the table and add 1 to this?

 

[varunvithalani]

Thanks for your reply,
About the dates thing let me explain further.
In the a table, one of the column have been set as Date.

Now when the dates have been manually entered, say 30-Dec-10, 31-Dec-10, and so on. I should now be able to manually generate 4 months of dates without the user having to input anything. It becomes tedious for the person using the booking system to manually enter all the dates every time by choosing. Since this booking system is based on a calender year, I would have to increase the dates by 1.

I hope this answers your question.

Thanks.

 

[acarter1]

Hi Mate,
As i said before i am not too experienced on this type of thing.
I'm guessing you need a loop to add a variable to a table until the variable is equal to a certain value something like:

Private Sub Command1_Click()
Dim MyConn As ADODB.connection
Dim dt1 As Date
Dim dt2 As Date
Dim dtTmp As Date
Set MyConn = New ADODB.connection
Dim MyRecSet1 As New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Datasource=**DBLOCATION HERE**"
MyConn.Open

dt1 = Forms!BankHol!DateFrom
dt2 = Forms!BankHol!DateTo

For dtTmp = dt1 To dt2
Set MyRecSet1 = MyConn.Execute("INSERT INTO *TABLE* (*DateField*) VALUES (dtTmp)")
MyRecSet1.Update
Next
End Sub

I am awful with loops etc so please test this before use and seek advice from someone else.

Regards,
Alex