Transactions In Access
Aug 14, 2005
Hi, folks.
I have to write a PHP web front-end to a client-server MS Access applicatin, and I think I've hit a stumbling block.
I use ADOdb as my db abstraction layer, and it has transaction functionality built in. I've tried it with Access, and while according to the output of the code the transaction has worked, I see clearly that it has not worked.
If I insert an intentionally malformed statement as part of the transaction, none of the other queries are rolled back upon the failure of said transaction.
I know this is an Access forum, not a PHP forums, but I've gotten zero response to this post on PHP forums.
Does anyone know if there's a way I can implement transactions for Access via PHP?
Cheers and thanks very much in advance.
Pablo
View Replies
ADVERTISEMENT
Nov 23, 2004
I am trying to run a transaction in my databases and I’m having problems with rollback. I have an append query and a delete query that I’m trying to run Just a test I begin the transaction, run the queries and then rollback the transaction. For some reason the rollback isn’t working. Please look at my code and let me know what I’m doing wrong. Are transactions not available in DAO? Also, how do you tell if all records were updated – How do you know when to Rollback?
Sub ArchiveRecords()
Dim db As DAO.Database
Dim qdfArchive As QueryDef
Dim qdfDel As QueryDef
Set db = CurrentDb
Set qdfArchive = db.QueryDefs("qry0201ArchiveLayoffs")
Dim cnn As Connection
Set cnn = CurrentProject.Connection
Set qdfDel = db.QueryDefs("qry0202DelArchiveLayoffs")
cnn.BeginTrans
qdfArchive.Parameters(0) = 1
qdfArchive.ExecuteqdfDel.Parameters(0) = 1
qdfDel.Execute
cnn.RollbackTrans
End Sub
View 1 Replies
View Related
Nov 29, 2014
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG)
Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer
Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String
Dim Lim As String, ITNO As Integer
[code]....
View 8 Replies
View Related
Sep 17, 2007
Hi there,
I have one table, called tblTransactions, which contains records on security market transactions. For each buy and sell order, respectively it contains one dataset with columns Date, Ticker (i.e. the unique identifier of each security), Quantity (positive for buy, negative for sell orders) and Price (at which the trade was executed).
The following SQL query code gives me then the composition of my security portfolio as per any arbitrarily chosen date (here 1/10/2007):
SELECT [tblTransactions].[Ticker], sum([tblTransactions].[Quantity]) AS TotalQuantity
FROM tblTransactions
WHERE [tblTransactions].[Date]<=#1/10/2007#
GROUP BY [tblTransactions].[Ticker]
HAVING sum(tblTransactions.Quantity) >0
ORDER BY [tblTransactions].[Ticker];
For instance, for some trades like the following...
Data --- Ticker --- Quantity --- Price
01/01/07 --- AAA --- 50 --- $50
01/01/07 --- BBB --- 25 --- $75
01/15/07 --- AAA --- 30 --- $60
...it results in the following query results:
01/10/07:
Ticker --- TotalQuantity
AAA --- 50
BBB --- 25
01/20/07:
Ticker --- TotalQuantity
AAA --- 80
BBB --- 25
However, I would like to add a column to this query, which gives me the number of trades for each particular stock. For instance, the query result based on the sample data above should look like follows as per 01/20/07:
Ticker --- TotalQuantity --- NumberOfTrades
AAA --- 80 --- 2
BBB --- 25 --- 1
I have absolutely no idea how to achieve that. I would be very happy for any suggestions.
Best regards,
JapanFreak
View 4 Replies
View Related
Oct 20, 2005
I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.
I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.
I do not want to use record by record VB because it is slow and I have a million+ records.
Can anybody help ?
Transactions Table (input)
Account Period Amount
1011100
1015 100
Account Summary (desired output)
Account Period Opening Movement Closing
101 10100100
10121000100
10131000100
10141000100
1015100100200
PS I need to run on Access and SQL Server
View 1 Replies
View Related
Jan 28, 2008
Hi,
I'm in over my head with something I've been asked to do at work as my access skills are pretty basic.
I need a sample database containing PRODUCTS and SUPPLIERS, that lets you book IN stock received from the supplier (and adds to the stock level) and lets you book OUT stock issued to staff (and reduces the stock level)
I'll have a go at reorder levels, form design, stuff like that myself, but I'm having trouble with these very basic requirements.
Does anyone know of a sample database that I can download to learn some of this from?
View 2 Replies
View Related
Jun 24, 2013
I need to create distinct transactions for each invoice, so that if the static data changes, such as rates of charge, it will not automatically update any history, where a historic rate has been charged.My current thinking is that I create a table and effectively archive all invoices that have been created, as they are produced.
View 4 Replies
View Related
Jul 12, 2005
I am building an application for a small clinic. In the interests of good database design, I don't want to be storing numbers that are derived by calculation.
A patient with insurance will have a deductable that will be entered into a field on their first (ms access) transaction form.
A calculation will put the remaining deductable into another field. No problem.
However, the NEXT visit has to look back to find previous transaction records, again perform the initial calculation and then perform another for this transaction, and again the next time and the next until the deductable reaches zero.
Additionally, since I'm not storing these values, if I pull up an old transaction form, that form has to re-perform the calculation ONLY on transactions from that date and earlier. It must not reflect visits that took place later.
So, this field must query tblTransactions, filtered by the name of the patient (a combo box called tblTransactions.Patient_Name that looks at tblContacts), the transaction date/time (stored in two date/time fields, cleverly named tblTransactions.Date and tblTransactions.Time), and the total deductable value stored in a field called tblTransactions.Deductable.
As someone who has never coded anything more complicated than DOS batch files, I'm at somewhat of a loss as to how to go about this.
Any assistance, code-wise and which Property value to put it in would be MOST appreciated.
View 1 Replies
View Related
Mar 21, 2008
Hey people, hows it going? Quick question
I am creating an EPOS system for a bookstore and I have a many to many relationship between the transactions table (tbl_transactions) and the products table (tbl_products) using a link table (tbl_linktblproductstransac). The below diagram shows what i mean below:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
I made it many to many because 1 transaction can have many products on it and 1 product can be on many transactions.
Now what I need to know is how will i make records with more than one product on one transaction (in the table itself or in a transaction form). I would really appreciate if someone could help. Thanks :) and have a good Easter
View 2 Replies
View Related
Nov 30, 2006
i think this post may benefit others because to me it seems like a good idea,
a listbox displays 2 fields from your database
job table
id (autonumber primary key)
pay (text)
here are 5 records,
1,paid
2,paid
3,paid
4,unpaid
5,unpaid
if you make a form with a listbox showin these records..
is there a way to make the unpaid records appear as red?
View 9 Replies
View Related
Oct 15, 2013
I am using the code below to evaluate values for each transaction to determine the median:
Function MedianF(pTable As String, pfield As String, pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Inputs: ? medianF("Values", "Industry") <enter>
'Output: results are currently incorrect
'*******************************************
Dim rs As Recordset
Dim strSQL As String
[code]....
The code generates median results based on the value but not the correct ones, as I check results in Excel using the Median function. I need to see the median for the transactions within the same industry and I do not know how to do that . Below are some sample transactions including the Industry, Value, mymedian (which the current results based on my code) and the expected median result (which I calculated in Excel based solely on the transactions listed here). Note that mymedian results listed in the 3rd column are determined using the complete data set (over 2000 transactions). I only included a few sample transactions below to show that I am currently getting 2 different median results: 44.62 and 50.82. Also be aware that I do not want to evaluate any zero value transactions, which I believe I am handling in the strSQL line above where I indicate <>0 . I need to see a Median result for each industry and the result should be the same for all transactions within the same industry.
Industry Value mymedian Expected Median
Aerospace 18.01 50.82 40.45
Aerospace 53.02 50.82 40.45
Aerospace 109.18 50.82 40.45
Aerospace 61.54 44.62 40.45
[code]....
This is how I am passing the values from the query to the module, passing the value of Period2 if Period 1 = 0, else passing Period1:
mymedian: IIf([Period1]=0, medianF("qry_My_Query","Period2","Classification_I ndustry"), medianF("qry_My_Query","Period1","Classification_I ndustry"))
View 14 Replies
View Related
Jan 28, 2013
I am using Access 2007 and have inherited a database. It has linked tables of Excel spreadsheets that are information extracted from our main system. The user wants developing a much faster and customized approach to billing the customer based on payroll related information.
There is a table of payroll billing transactions and it has the following record lay-out,
job_num, employee number, employee name, union group, rank, regular hours, overtime hours, other hours, trans_date, seq_num
6430, 2301, john doe, 1, F, 8.0, 0.0, 0.0, 12/01/2012, 254333
On December 1st John Doe worked 8.0 regular hours as a pipefitter foreman on job number 6430 and this is record (sequence) number 254333.
There is another table that defines the union group. I am not concerned with this information at this time. The seq_num is a sequentially and uniquely assigned number given to each billing record in the main system.
There is also a Rate Master table and it has the following record layout,
job_num, union group, rank, regular_billing_rate, overtime_billing_rate, other_billing_rate
I am struggling as to how to relate these two tables so that I can develop some queries from it.
View 2 Replies
View Related
Jul 20, 2015
I have a pop up reports tied to my form so the usere can press the see list report of all the equipment they just scanned in or out of the building. I am useing the Date/Time data type (General Date) What I would like to get out of the query is when the user clicks the button it will show all entries from the time right before the button is pressed AND through 5 minutes earlier. (That would be the time that the user would of started scanning the equipment.
This is my current criteria : Between Now() And Date()
This is returning all transactions for the day. I really only want all transactions for that session.
View 3 Replies
View Related
Apr 5, 2013
I am trying to keep records of GST transactions.
I have a problem in figuring out how to do calculations in a query.
How to have the calculation in the attached MDB and Document for field #105_SubTotal and the field #115_PaymentEnclosed.
View 7 Replies
View Related
Jun 19, 2012
I am trying to calculate the stock level for my spareparts. I am aware of that I am not to update the stock level in my table but calculate the total from the transactions.
View 9 Replies
View Related
Jul 29, 2014
I have a set of transactions with a Unique ID field and a date. I want to add a field based that gives me the sequence of events for each Unique ID in order of when it happened.
For example, if customer X has 6 transactions, the sequence field would have a number (1-6) in each record that corresponds to the order in which those transactions occurred. The first transaction would have the number 1, the second would have the number 2 and so on.
View 3 Replies
View Related
Apr 3, 2014
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
View 8 Replies
View Related
Jan 29, 2007
Hello,
I have installed Microsoft Access 2003 and Microsoft Access 2003 Developer Extensions. I can create Access application by using the Package Wizard.
But after I upgrade from Microsoft Access 2003 to Microsoft Access 2007.
I can't use the Package Wizard.
How can I do to solve this?
Thanks.
View 1 Replies
View Related
Jul 10, 2006
Hi,
I have been using "fSetAccessWindow (SW_HIDDEN)" in the open event of my
main switchboard to hide the grey Access screen from being visible behind
forms, reports, etc in this db. But when I started using a .vbs script file
to launch the db, the Access window stopped being hidden. (The reason I use
the script for launching is that it temporarily sets the db's security level
to 1 which stops the macro security warning from coming up.) Apparently MS
knows about this happening and released a technique to rectify the situation,
but I don't understand their instructions. It looks like they expect one to
have more understanding than I have. Could someone help me get this
impliemented? Here is the article:
http://support.microsoft.com/kb/167659/en-us
Thanks!
View 2 Replies
View Related
Apr 13, 2007
Hi!
This is Kishore, working on VB Project which is using MS-Access95 as backend.
Now, i want to change the Database login Password.
Could anyone guide me in this context.
Regards,
Kishore
View 4 Replies
View Related
Sep 9, 2005
Using Access's User and Group Accounts or Using a Login Form to access database?
I've been researching on how to make a database secure. How to create User and Group Accounts on access, I see the step by step instructions and tried it out myself.
I also saw some sites where they give an example of a Login Form and how to create one.
My question is do you need to create both. First create the users and groups to permit or deny access to certain forms and then have a login form?
But would that mean that they'd have to login twice? Once when the database opens because it activates the db security that was created and then login again in the login form that was created?
Also when the user logins in and clicks on the cmd button on the form which opens up another from, frmWorkLog, I have an Employee field. This field I want it to have the user's name entered automatically and "locked". So that info, employee name, is extracted from the user's login. So then the user can only see his or her records only and no one elses.
How would I go about creating that. Hope I made my explanation clear.
Thanks in advance.
View 3 Replies
View Related
Apr 15, 2007
I have two database applications and they are:
- the (A) application is for administration use.
- the (B) application is for normal users use.
the idea is that: I made the (A) application for administrators who have full control over the database objects (tables, forms, queries, and so on ...).
the (B) application I have created for normal users who will have only to use forms to insert some data and display data only.
but the two applications has a respective table called "vacation request" table. where I linked them, so the both administrators and users can share the data.
The real question is that: How can I prevent the users from seeing the database objects in their application. I used the database options which have helped me in hidding the database objectives when the users open the application, but unfortunately they managed to access to the database objects by pressing the special keys.
I would like to have an access to the (B) application when I want to make some modifications to the forms and then lock it from users where they only have to use the forms for requesting vacations and view the vacations.
View 1 Replies
View Related
May 3, 2014
I have a simple access search form , that's based on a query that fill parameters from the form textboxes, when the access form loads its keeps prompting for parameters value which looks ugly .. I want to open the form, displaying all the records in the table and filter when i click search ..
I have a data entry entry form, that i want to generate success message after successful insert in database. I have done it in the button event if no error happens, still if i left all fields blank and clicked save, it displays the message ..
I need to change that to display please fill the textboxes then click save , and display success message when the row is actually inserted ..
Check the following attachment for sample.
View 14 Replies
View Related
Oct 24, 2013
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM
qryContractListSummarybyDateContract3TYPEBREAK WHERE
qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri
Masonry NSW');'
View 12 Replies
View Related
Jun 19, 2007
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
View 2 Replies
View Related
Sep 13, 2007
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
Emily
View 14 Replies
View Related
