What Programs/Software Should I Use?
May 12, 2005
I am hoping someone can give me guidance on a fairly major project I am working on.
I am responsible for producing lots of figures, charts etc. each month and the current process takes a very long time!
I have been charged with improving this process, which currently includes the following:-
Slightly changing some CSV files each month
Importing the CSV files to an Access database containing lots of other data and information
Running some queries off an SQL database and importing the results to the access database
Running a few queries in Access
Exporting the query results to Excel
Manual manipulation of the Excle files
Producing graphs and tables in excel
I am fairly experienced in Access and know a little Access VBA
I know a bit of SQL to run queries from SQL query analyzer
I have dabbled with VBA in excel
But I wouldn't really call myself a programmer
I need to automate the process as much as possible, but I am not sure which way to go. I would need a database to combine the data from CSV files and other information as current, but also need to build up a historical store of data.
I would then want some kind of front end to select the months and year to look at, plus different categories etc. then produce a table and chart to excel or easily exported to excel for distribution to other people.
Would a combination of Access and Excel using VBA be sufficient for this?
A colleague has mentioned using SQL, in particular MySQL, which is downloadable for free.
They have also said that you can develop a front end in visual basic and there are programs freely available where you can build a visual basic front end by drag and drop methods.
They also mentioned that you could build a front end using Java.
I have also read bits on this site about ASP and PHP and how they can be used as a front end with Access as a back end database.
In short, I am getting confused about which combination of tools would be best suited for this job!
Any assistant/opinions/thoughts would be greatly appreciated!
The output tables/charts etc. would have to be distrubuted to a large range of people in the company.
The database or front end would just be viewed by a couple of people.
Many thanks!
Sasha
View Replies
Nov 15, 2004
I'm having an issue with two programs. One searches the DB and emails the results, the other searches the DBand prints labels from a report. When only one is in there the program will run fine. But when I try to implementboth at once then I get an error message that is something like: The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: SQLSafeThe way the form is setup I am using the same text box to enter my search criteria and the same option groupbox with my check boxes for what I'm searching for both programs. Is this possibly the conflict? Am I going to haveto have seperate option groups and search criteria boxes for each program? It would be nice if I don't because thiskeeps the look of my form clean. Thoughts?Code for Search Database and Send Email
Code:'Designed by M. Walts'Important information! this code requires a reference to the Microsoft DAO object libraryOption Compare DatabaseOption ExplicitPrivate Sub cmdEmail_Click()'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE City = '" & txtSearch & "'" End SelectstrSQL = "SELECT EMail FROM tblUser " & strWHERE'run the query and get the results into the recordsetSet rst = CurrentDb.OpenRecordset(strSQL)'Loop through the recordset and add all the EMailsDo While Not rst.EOFstrRecipients = strRecipients & ";" & rst!EMailrst.MoveNextLoop'remove the first ; from the strRecipientsstrRecipients = Right(strRecipients, Len(strRecipients) - 1)MsgBox strRecipientsDoCmd.SendObject , , , , , strRecipients, txtSubject, txtBody, Falserst.CloseSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End FunctionCode for Search Database and Print LabelsCode:Private Sub printLabels_Click()'Edited by Nicholas Brown, original code design by M. Walts'Important information! this code requires a reference to the Microsoft DAO object library'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE City = '" & txtSearch & "'"End Select DoCmd.SetWarnings False DoCmd.DeleteObject acTable, "tmpClients" strSQL = "SELECT tblUser.* INTO tmpClients FROM tblUser " & strWHERE DoCmd.RunSQL strSQL DoCmd.OpenReport "Labels", acViewPreview 'just view for testing, switch to print mode later acViewNormal DoCmd.SetWarnings TrueSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End Function
View 3 Replies
View Related
Oct 29, 2005
Hi,
just starting out building a database product for mineral collectors and wanted to know what to do went it is complete.
If you want to issue the finished collectors data base as an application to other users do you need a program generator?
I have heard o the Access tollbox, does this do carry out the generation?
Thanks
View 1 Replies
View Related
Jan 31, 2008
I am trying to upscale several databases from mdb to sql server. Unfortunately I am not sure of all the programs that use these database tables. Is there a way for the mdb to store what programs request data from it or alternatively the username. Im thinking not but worth a check.
View 2 Replies
View Related
Jan 10, 2006
Hi All,
I need some advice as to what is the best way for me to update a program in 3 different locations.
I have developed a program that I now have running in 3 separate locations. Each location uses the same program structure and tables, but they will have different data.
When I make a change to the one in our office, I would like to update the other 2 offices so that we all are using the latest version.
CURRENTLY: I make a database file with the “forms, Reports, Queries, and modules” necessary to accommodate the changes. Then I, email the file to the other offices. I then try to walk someone there at the office through; “Get Files: Import”, then delete the old files and rename the new ones. This is risky!!!!!:(
I know there is a better way of doing this, I just don’t know it.
Can some one help me on this issue?
Thanks for all your help….ENVIVA :)
View 1 Replies
View Related
Jan 9, 2014
I have a query called "Program Status Count" that keeps track of the current status of various programs. There are three statuses: Closed, Deficient, and WIP.On the Query, I used "Total:" row and have two fields: StatusID field set to "Group By" then in the next column I have the same StatusID Field set to "Count" (it auto-renamed itself to CountofStatusID)...which on the query does what it is supposed to do:
Closed, 2
Deficient, 1
WIP, 1
When I go to the Report Design and try to make a Pie Chart (or any graph) it asks me what query I want to use, so I point it to the Program Status Count Query and select the only two fields I have in the query.This is the Row Source Code:
SELECT [StatusID],Count([StatusID]) AS [CountOfStatusID] FROM [Program Status Count] GROUP BY [StatusID];
Sometimes, it will show the appropriate fields but then it will disappear in Report View, switching to Print Preview does not fix the issue. Actually, it often deletes the correct information in the chart and use the PK autonumber instead of the words.Othertimes, the default data shows up and nothing I do changes it. I have even done the copy/paste of the query table in the report (doesn't work when the graph needs to update.
View 3 Replies
View Related
Nov 22, 2007
Hi
Has anyone ever got this error message before? I’ve never came across it and I don’t know how to fix it. The error message displayed when I added three extra labels to a form then tried running it. At the beginning, it took more time than usual to think about it then the error message was displayed. Now I can’t open my form in design view nor can I run it! It’s totally inaccessible.
The error msg is attached.
Any help will be very much appreciated,
B
View 4 Replies
View Related
