Add Records To Table In Bulk (not One Record At A Time) Via Multidimensional Array
Aug 13, 2015
I'd like to add 600+ records at a time to a table (or at least a lot) and do this 1200 or so times. Yes, this is a very big table, its okay and the size is not an issue.
The key here is that speed is very important. I DO NOT want to AddNew and Update a recordset 700k times (one for each new record). I want to add 600 records or so 1200 or so times.
I am open to any way of doing this in VBA and/or SQL in MS Access; however, I am thinking of building an array that has 600 rows and 16 columns, then somehow add that to the table all at once. I will size the array down if it causes problems. That isn't the issue. The issue is how to add a 2d array to a table in bulk (all at once).
Code:
Sub test()
Dim dbLocal As DAO.Database
Set dbLocal = CurrentDb()
Dim sql As String
'build my array
Dim x(0 To 1, 0 To 1) As Integer
[Code] ....
View Replies
ADVERTISEMENT
Jul 9, 2014
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
View 2 Replies
View Related
Jun 9, 2013
I'm trying to upload a csv file into an Array then add records to a table. I have the following code which gets the information from a csv file which works fine.
Open filePath For Input As #1
Do While Not EOF(1)
Line Input #1, MyData
Problems:
1. When i try to load this into an array , it does not return all the information. It will if a smaller amount of data is sought. For example if only 1 months month of data is requested. Is there a maximum data limit that can be parsed into an array with this method?
2. I'm not quite sure how to parse individual lines (records) to update a table in access. I have provided the code that I'm trying to use to accomplish this below. From code below am I doing this correctly?
3. For each record that is created in the database I would like to add a ticker string to the record for later querying. Can this be done and am I on the right track from the supplied code?
Private Sub ImportData(filePath As String, ticker As String)
'On Error GoTo Errorhandler
Dim arrData() As String
Dim MyData As String
Dim i As Integer
Dim Db As DAO.Database
[Code] ....
View 6 Replies
View Related
Mar 22, 2005
Hi, I need help with inputting multiple values in a table
I am using below statement in asp file and it works fine, when I input one value of name in form , as it takes that value and just input into the table. If it already exists then it just displays error message that this name value already exist in table as name field has no duplicate checked. I need to input multiple records now instead of just one.
The current statement I am using is
<%
Name = replace(trim(Request("Name")),"'","''")
SQL = "Insert Into table(tableName) Values('" & Name & "')"
objCon.Execute SQL
%>
But now I want to send multiple values from the text box instead of just one value, so I need a loop here. The values in the text box are separated with enter.
Please let me know how do I run this query in loop that it will input one name first and then input the second value and then third so forth. (input multiple records instead of just one).
Can you please give me the SQL statement for ACCESS or asp querry.
thanks
View 1 Replies
View Related
Aug 13, 2011
How can i retrive Bulk Data - from Different database into this database table. At the moment i loop through all the records which is not good idea...i want it to be real quick without linking the table...
I heard something like Insert into statement can work but have never used one....
View 3 Replies
View Related
Nov 4, 2014
I've a table containing ebay sales information, let's simplify it...
Code:
buyer email product quantity price total Receipt Number
A Smith a.smith@acme.com gloves 1 $10.00 $10.00 1
P Brown p.brown@jones.com hat 2 $15.00 $30.00 2
After a lot of faffing about (& learning about sub reports), I've now got access producing a sales receipt from the data....it's in the form of a report (the report generated is one long continuous report, with each page in the report relating to a sales receipt number)
What I want to do now is email each page of the the report (like I say, each page is essentially an individual sales receipt) to the associated email address in bulk.
Therefore there may be 20 sales receipts in a long continuous report, how would I break them out one by one to a pdf file & send it to the correct email address which is contained against the sales receipt number in the same table?!!!
View 4 Replies
View Related
Oct 12, 2004
Hi all,
I had a table which had a yes/no check box. Is there any way to update all the checkboxes
in the table based on a value in a field same time?????
thanks
View 6 Replies
View Related
Dec 3, 2014
I have a table which has a primary ID and then date/time then an empid field relating to an empid table,
Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whichever is easiest?
View 6 Replies
View Related
Apr 11, 2008
Hi, I have a problem deleting a specific row and field that my query have.
What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4
what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example
Dim IntRow As Integer
Dim IntCol As Integer
Dim varData As Variant
For IntCol = 0 To numFields Step 1
For IntRow = 0 To numRows Step 1
If (rst.EOF = False) Then
'Check Field 1 for value 47-72 to delete
Else
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then
varData(IntCol, IntRow).Delete
End If
End If
Next IntRow
Next IntCol
In the example Above my syntax is not correct for :
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72)
and for varData(IntCol, IntRow).Delete.
can anyone help me to find the exact syntax i need to use to execute the program above?
THANK YOU!
View 2 Replies
View Related
Jul 5, 2005
HI all,
I am still new in Access databases :o
I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().
When this is done I have create calculations with extracted records using agregate functions.
I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.
Please help! :confused:
View 7 Replies
View Related
Feb 18, 2008
arrLocations = Me!customerBranchLocations.Value
'MsgBox (arrLocations)
arrParams = Split(arrLocations, ";")
For i = 0 To UBound(arrParams)
MsgBox (arrParams(i))
strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & ""
'MsgBox (strSql1)
DoCmd.RunSQL strSql1
Set rs = db.OpenRecordset(strSql1)
Dim count
count = rs.RecordCount
Next
Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?
Thx.
View 2 Replies
View Related
Jan 23, 2013
How can I add an ID-number that always increases (by 1) each time I add a new record to a table (using a form)? I want to always use some info from the record that was added last to fill in a different form. The reason for my question is that I heard that the autonumber does not necessarily increase each time a new record is added...
View 1 Replies
View Related
Oct 25, 2013
I need to reset the autonumber each time I delete/append records in a table. Best way?
View 5 Replies
View Related
Jan 24, 2013
I need to fill a pre existing table with data based in an array.
View 2 Replies
View Related
Jan 8, 2013
I have a table that contains the following;Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]
The data is uploaded to my table from a 3rd party database source via .csv file.I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.I have attached a file that shows the data in a format I would like to see in Access displayed in a form.
View 2 Replies
View Related
Jul 3, 2013
I am trying to automatically update one table to another each time a new record is added to my form, I have tried using the Update and the Append Query is there another way to do this without using code?
View 4 Replies
View Related
Nov 10, 2014
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
View 3 Replies
View Related
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
View 8 Replies
View Related
Sep 8, 2014
Any Single line of code available?
View 2 Replies
View Related
Oct 14, 2004
Hi,
I am creating a league table for my soccer team.
I have a database with these tables:
1. teams
2. league_table
3. fixtures
The Fixtures Table has a lookup where it pulls in the opposing teams - home_team & away_team. There is also Home_goals & away_goals. How can I get the fixtures table to update each teams goals in the league_table table where these details are held.
Anyone any ideas on how this is done?
Thanks in advance.
View 1 Replies
View Related
Jan 10, 2007
though my actual application contains 6 tables, I will give an example with 2 to simplify my question:
Say I have a simple DB with a Company table and an Employee table....the only attributes are CompanyID (autonumber/PK) and CompanyName for the Company table and EmployeeID (autonumber/PK) and EmployeeName for the Employee table.
Now I have an excel spreadsheet with Company Names and Employee Names on a worksheet. How can I import these to both tables and keep the relationship of who works where? Keep in mind that I have 6 tables in my actual DB.
View 3 Replies
View Related
Aug 22, 2013
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
View 6 Replies
View Related
Oct 5, 2005
I am pretty new to MS access and have a question that I have not been able to figure out. I am making a database with a form to input the data in to the database. The only thing is I want to be able to search/lookup/record data by the products ID code and not by its record number. For example, product 1234 instead of entry number 1. How would I go about this? Thanks for your time!
View 1 Replies
View Related
Jan 26, 2005
My situation:
I have a table with duplicate ids. When I have a forum popup another form, the forms are linked by ids. This works great except for one problem. There are duplicate ids. So if I pop up a form then it will go to the first record that that id exist. This is not good if you are going to add another record. Therefore, I need to create a unique variable for a record. The autonumber would be a great variable but it seems I cannot use it to filter. Unless someone can show me how. Is there another way to create a unique variable automatically for a new record?
View 5 Replies
View Related
Jan 22, 2014
How can I combine several records in a table into one record?
Suppose that I have a table like Table1 in the attached image.
Then I want to combine all records with the same value for Key1 in one record.
The result is shown in Table2 in the attached image.
I would prefer to do it using SQL only, but I guess that this is not possible. Is it possible?
Alternatively I could accept to turn to VBA that could do it. Any good links about this?
View 3 Replies
View Related
Mar 27, 2014
I'm using access 2010. I want to put some code behind a button that will allow the user to add selected products in a listbox to which ever operation is selected in a combobox by creating records for each product / operation combination in a third table:
tblProducts
tblOperations
tblOperationProductMM
- ProductID
- OperationID
I have a form with a combobox on top that allows the user to select the operation for which they want to add products. There is also a listbox that displays all records in tblProducts. The user can select one or more products and then the idea is that they can then press a button that will use the selected record IDs from the list box and the record ID from the combo box to create new records in the many to many table.
View 5 Replies
View Related