Modules & VBA :: Ms Access Lock Table While Inserting Data

Oct 31, 2014

In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table

This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.

On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.

But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ?

View Replies


ADVERTISEMENT

Inserting Data From Excell To Access Table

Mar 23, 2005

I am currently using access 97.

I am trying to read from an excel file that has the same format but changes data every day due to reports that I run from a different program. I just export them to this excel file called test.xls

What I want to do is import this data from the excel file and insert it into existing data in my table. I need to this every day.

Currently I cannot get the data to append. I can only insert the data once and cannot get it to append. I tried using macros, but think it would be better to use VB.

Any help you could provide would be great.

Thank You

View 1 Replies View Related

Modules & VBA :: Inserting Into Table From Form

Dec 29, 2013

I am building an application using MS ACCESS for a local volunteer ambulance corps and have almost complete it except for the last challenge. I have built many applications like this, with an form to add, modify and delete option for a single table. This time, the accountant asked for data to be saved before being deleted to another table so he can review it after it was deleted by a user and then delete it from that "backup" table. I am at a loss to write the code. However, being a reader of this forum taught me many things in the past and I decided to join and ask this query of the community. I know it is an insert command and I have created a mirror table for the real one with all of the twenty or so fields. The fields are the usual name, address, city state and zip with telephone numbers and some other info.

View 1 Replies View Related

Modules & VBA :: Inserting Values Into Table With SQL

Sep 12, 2014

I'm trying to insert a set of values into a table using SQL.

Here's my code:

DoCmd.RunSQL "INSERT INTO tblAuditTrail([DateTime], [UserName], [RecordID], [Action], [FieldName], [OldValue], [NewValue])VALUES (" & Now() & ", " & User & ", & Me.CSM & ", " & 'EDIT' & ", " & 'Location' & ", " & Me.txtTranFrom & ", " & Me.txtTranTo & ");"

However, this returns a compile error.

I reference User as Environ("USERNAME") earlier in the code. Everything else pulls from the current table ("tblInventory").

I'm lost, since I've never attempted to use SQL in VBA.

View 3 Replies View Related

Modules & VBA :: Inserting Images In A Table

Oct 9, 2014

I'm new to vba programming and I've written a code which will insert a table in the current slide now i want to insert pictures in the cells of the table how can i do that?

This is my code:

Sub NativeTable()
Dim pptSlide As Slide
Dim pptShape As Shape ' code from google to create a table
Dim pptPres As presentation
Dim iRow As Integer
Dim iColumn As Integer

[Code] ......

View 2 Replies View Related

Inserting Data From Form Into Table

Nov 2, 2005

i have a problem adding the data from the fiels on the form into a table. I know you can just click on the navigation arrows and it will save the record but i want to use a submit button as this is more user-friendly and suitable for the work i'm doing.

I have attached the database... the form that needs the code for submit button is 'frmNew_JobStatus' and the table i'm trying to insert the data into is 'tblJobStatus'

can anybody have a look at what i've done so far and suggest how i can fix this... sample code would be very usefull as i'm not an access expert.

thank you all

View 1 Replies View Related

Inserting Data From One Table To Another Through Form

Aug 25, 2004

I want to design a form , please note I have beginners information regarding Access,
I have 2 tables , 1 table has Project No, Document No, DEpt NO. with all the information

There is another table-2 with Project No, Document No. and Resourcrce. There is not data
in this table

What I want to do is , I want to create a form where in I select the Project No. and
Dept No. When I select these items the form should list me the data in Table 1 for that
Project no and DEpt No. , after this data is listed , I will add the REsource data
and all this data has to be stored in table-2. I hope some one will be able to guide me on
how to do this.

View 6 Replies View Related

Forms :: Inserting Data In Table

Nov 9, 2013

I am new to Access and i am designing a payroll system for my company. I have created a Table with

EMP Id, Name, Basic Pay, Total Working Days, Actual Work Days, Earned Salary, OT days, OT Hrs, OT Pay..

Now i have created a form with all the above fields.and calculated the earned salary from the control source where Earned Salary= Basic pay / Total Working Days*Actual Work Days and OT Pay =[Basic Pay/Total Work Days/8]*150%*OT Hrs.

Now my problem is i am not able to populate the fields in the table.

View 4 Replies View Related

Modules & VBA :: Inserting Filename And Path Into Table

Jan 8, 2014

I have a table named tbl_imagepaths with fields:

ImageID
File
Folder

The command button has the code below, it works great and pops up with a window with the file name and path. I now just need the code to insert the file name and path into the table....

Code:
Private Sub Toggle7_Click()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)

[Code] .....

View 7 Replies View Related

Modules & VBA :: Inserting Field Value When Adding Record Using Data Entry Form?

Dec 14, 2014

I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so

************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********

Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.

************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********

making sure I can insert this value once retrieved.

View 7 Replies View Related

Inserting Data From Two Tables Into Another Table Using A Form

Oct 22, 2007

I am new to access (using Access 2003) and am having trouble working with forms. Here's what I want my form to do:

-Use a combo box to select a specific system
-Given the selected system, pull up ID numbers and descriptions (in separate text boxes) from two separate tables corresponding to that one system
-Navigate through those ID numbers/descriptions from each table independently to find ones that match
-Store the ID numbers of the ones that match into another linking table

The biggest problem right now is being able to navigate through the different table ID numbers/descriptions and add both ID numbers to a row in a different table. I've tried using a combo box with the INSERT INTO statement into the code builder, but I keep getting syntax errors.

Does anyone have any suggestions on a better way to do this?

Thanks!

View 3 Replies View Related

Queries :: Inserting Missing Data In A Table

Jan 10, 2015

How to fix some records in my access table. It is a huge table more than 12k records!

In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.

I have three columns one is the student ID, Year, term1 and term2

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005

In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311

ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005

View 3 Replies View Related

Modules & VBA :: Show Lock Status Of A Record When Other User Is Editing Data?

Mar 27, 2015

I have a form and I want show a message on the form when the it is locked as another user is editing the data in a particular record.

I know the record selectors show the records lock status but it a very tiny symbol which will mean nothing to the users of the database and anyway I don't want record selector bar on the form. How I would do this???

View 2 Replies View Related

LOCK TABLE In Access Db

Nov 12, 2007

I'd like a code to lock a table in my access database so that it can not be mistakingly deleted by the user.
It's a very basic database program i am using for basic reporting purpose. I'd like to lock the master table from the user. How can i achieve this? Any ideas?

View 3 Replies View Related

General :: Lock A Table In MS Access?

Aug 29, 2014

Is it possible to lock a table/macros/forms/reports in MS Access?

View 3 Replies View Related

Forms :: Method Or Data Member Not Found When Inserting Values To A Table

May 13, 2013

I am getting method of data member not found for the following code when inserting values to a table. Form has three frames. Add/Insert is on OnClick Event.

MsgBox "First Value, " & Me.txt_FSNo
CurrentDb.Execute "INSERT INTO BackchargeLog (BC_FSNo, BC_FContract, BC_FPurchaseOrder, BC_FProjectArea, " & _
" BC_FTitle, BC_FDetailDescription, BC_FSupplierToBeCharged, BC_FSupToBeChargedNumber, BC_FSTBCContactName, BC_FSTBCContactNumber, " & _
" BC_FSTBCContactEmail, BC_FIncidentReportedDate, BC_FInitiatedDate, BC_FEstimateAvailable,

[Code] .....

View 7 Replies View Related

Modules & VBA :: Error 3211 - The Database Engine Could Not Lock Table XXXX

Oct 20, 2014

I have a DB with multiple import processes to take in information and populate various tables. This has been in place for some time and works with no issue.

The import process involves the creation of a local staging table, to which I import the received data, normalise it according to my own table structure, remove any duplication (i.e. same data imported repeatedly) and append the cleansed data to the main tables.

I've just added a validation step to check for data integrity. In other words, for duplicate data, rather than simply purge it as I was doing previously, I now check to see if the data has changed in any way and prompt the user if necessary (with the option to either ignore the change or update the existing record)

However, since I added this extra step, I am getting the following error message during my import process :

Error 3211 : The database engine could not lock table 'tblStaging' because it is already in use by another person or process

It only appears a) when a data change is detected and b) when the option to update is chosen, and seems to occur after the update has taken place (i.e. I can see the records suitably updated)

But I can't replicate the error while debugging so it seems to be a run-time issue. It's definitely not another user as the staging table is deliberately local (specific to that user's front-end)

I suspect I can probably solve the problem by adding some kind of DoEvents call or brief pause somewhere but the problem is, I don't know where the problem is occuring, so I don't know where to add the line? Or if that would even solve the problem at all?

Is there an easy function I can call to check if the staging table is locked? Which I could then use to debug and identify exactly where the lock is causing a problem? Or even apply the DoEvents to release the lock? (It must eventually get released as if I step through the code, I don't get the error?)

View 5 Replies View Related

Modules & VBA :: How To Lock A Table - Stop User Adding Or Altering Records

Sep 19, 2013

I have a form that allows a user to complete a stock take. I would like to stop other users from receiving or despatching stock while a stock take is in progress.

Is there a way I can lock a table, or stop users adding or altering records that match certain criteria. i.e. don't let users receive or despatch stock from with a locationID of 'A'.

View 4 Replies View Related

Modules & VBA :: Copy Data In Sheet1 Using Sheet 2 And Access Table Data?

Aug 5, 2014

I am using Excel/VBA as a frontend and Access backend. The sheet2 stores the queue name and Queue number. We have to update the sheet1 from column L to column O by looking for the values from the Access table for the date selected from the comboboxes. Now In sheet 2 , it says Queue number and in actual in access table it is the combination of Type & Type1 & Type2. So we have to look for Type & Type1 & Type2 in the table and find out total Batches ,Total Envelopes,Total documents and total pages and then store the values in the ExcelSheet1 from column L to column O.

The following formulas will be used in the select statment:

Total Batches = count(BatchNo) for date selected
Total Envelopes=sum(Envelopes) for date selected
Total Documents=sum(Cases) for date selected
Total Pages=sum(Pages) for date selected

View 14 Replies View Related

Modules & VBA :: Importing Excel Data To Access Table

Mar 25, 2015

I have a VBA function to syncsuppliers as below

Function SyncSuppliers()
On Error GoTo errhandle
Filename = DLookup("SupplierPath", "Setup", "SetupActive = True")
If Filename = "" Then
Exit Function
End If
Set xlapp = CreateObject("Excel.Application")

[code]....

The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.

View 2 Replies View Related

Modules & VBA :: Adding Data In Listbox From Access Table

Jun 5, 2014

The following code gives me runtime error message "couldn't set the list property , Type mismatch".

Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim j As Long
j = 0

[Code] ....

View 3 Replies View Related

Modules & VBA :: Importing Data From CSV File Into Access Table

Jul 19, 2013

Code beneath worked perfectly with Access 2003.Now we switched to Access 2010 and it generates a table where data isn't put into my 77 fields like before, but seperated into 1 field, separated by some ;;;.I am not good with programming.

Dim rst_data As Recordset
Dim oldname As String, newname As String
DoCmd.DeleteObject acTable, "TBL_import_TPXP_Radi_Evvd"
DoCmd.TransferText acImportDelim, , "TBL_import_TPXP_Radi_Evvd", "N:APPLSHAREPRDQSIGMKTDISTPWBUSPB1815RADIEV IMPACT.CSV", False, ""

[code]...

View 2 Replies View Related

Modules & VBA :: Export Data From A Table In Access To Excel

Apr 16, 2015

i have the following code and it runs without error but when i want to open excel file, i have the following message and i can't open it.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_userinformation", "G:Rasteh MonaName.xlsx", True

View 3 Replies View Related

Modules & VBA :: Get Filtered / Grouped External Data Into MS Access Table

Oct 20, 2014

I have an Excel file with a name range "DBIAS" which identifies all database data.

Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.

I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.

I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.

View 3 Replies View Related

Modules & VBA :: Naming Range To Export Data To Access Table?

Dec 15, 2014

I am trying to write code in an excel worksheet to try to export excel data to a table in access.

The range in which the data is situated in excel varies. So the range address varies with the data. After a lot of searching I wrote the following code which works for a range which is fixed. The code is the following:

Dim acc As New Access.Application
Range(Range("b22"), Range("b22").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection
acc.OpenCurrentDatabase "D:PayRollFactoryJasminePayroll.accdb"
acc.DoCmd.TransferSpreadsheet _

[Code] .....

As one can see from the first two lines of code I am trying to first delineate the range and then to name it. The intent is to name the range "ghazla" and so to cater for the fact that the extent of data varies.

The code works with the fixed range ="Sheet2$B140". However what I need to do is to adjust the code so that I can insert "ghazla" as the range name so that this can vary.

View 5 Replies View Related

Modules & VBA :: Check If Data In A Closed Workbook Is Present In Access Table

Oct 15, 2014

I am using the following line of excel vba code to extract the name of excel file that I would like to compare with Access table data.

Code:
strFile = Application.GetOpenFilename("Excel Files,*.xls*")
textbox1=strfile

Now i want to write VBA code to check sheet name "Summary" in that workbook and check if any of column D value matches with Access table tblmain field "Reference" values. If yes then change the status field of tblmain to "Yes".

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved