Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record.
So here's my method. I created two tables with the same structure as the table I'm inserting from. One table, Split_Temp, is the one I'll be inserting to. The other table, Split_Insert, contains the "Blank" record, which actually just has the word "Blank" in an identifier field.
The problem is, after a while, the records stop inserting in order. The numbers in my Split_Temp table start jumping around in groups of < 20, so 116 might be followed by 122, 123, 124, 125, 126, 127, 117, 118, etc. I checked to make sure none of the fields have an index (so that I'm not just being shown the data in a certain order based on an index). There are no indexes. - With dbs
-
strSQL = "Delete from Split_Temp"
-
.Execute strSQL
-
End With
-
-
Do While intCount1 < intTotalRecords
-
-
With dbs
-
strSQL = "Insert into Split_Temp Select * FROM " & strWhatTable & " WHERE Pst_Seqnum=" & intCount1
-
.Execute strSQL
-
End With
-
-
Set rs = dbs.OpenRecordset("Select * from " & strWhatTable & " where Pst_Seqnum=" & intCount1)
-
-
strBM = IIf(Not IsNull(rs("Breakmark")), rs("Breakmark"), "")
-
-
If strBM = "##" Then
-
With dbs
-
strSQL = "Insert into Split_Temp Select * from Split_Insert"
-
.Execute strSQL
-
End With
-
End If
-
-
intCount1 = intCount1 + 1
-
Loop
-
5 2144 nico5038 3,080
Recognized Expert Specialist
I wouldn't use an additional table, but a UNION query and add a sequence number for the order needed.
Would look like: -
select ID, 1 as Sequence, field1 from tblWhatTable
-
UNION
-
select ID, 2 as Sequence, field1 from tblWhatTable where Breakmark = '##'
-
order by ID, Sequence
-
Getting the idea ?
Nic;o)
Could you clarify this a little, I'm having trouble following exactly what it is you are doing. You mention that you have to loop through a file and then go on to call it a table as far as I can tell. Are you ultimately needing to deal with a file or tables. What are all the fields in those tables? Is it that you have field full of id numbers and if you come across a certain number then a blank should be appended after that number (within the same column)? I apologize if I'm missing something obvious.
@janders468
Essentially, the table is in a certain order (with the field "pst_seqnum" being a numeric field in that order), but a blank record needs to be inserted after each record that has "##" in a certain column. I tried splitting the table into temp tables and putting the tables back together with the blank record in between, but my the orders were all mixed up. So I thought, if I insert one record at a time based on the pst_seqnum field, it would certainly be in the right order because I'm looping numerically based on this field. However, that does not seem to be the case.
@nico5038
I kind of see where you're going, and my initial results along this line are good, so I'll look into it. Still, I'd like to find out why my records are not inserting and/or displaying properly using my method.
nico5038 3,080
Recognized Expert Specialist
Guess you need to check the line:
strSQL = "Insert into Split_Temp Select * from Split_Insert"
Here no WHERE clause is used.
Using a query is much faster in processing as using recordset processing. In general queries are at least 6 times faster as code..
In your case, using a temp table, the database will also need regular compacting.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Yannick Turgeon |
last post by:
Hello,
We are in the process of examining our current main application. We have to
do some major changes and, in the process, are questionning/validating the
use of MS Access as front-end. The...
|
by: |
last post by:
I am updating MS access tables with data in an xml
document. I create two dataset, one for existing data and
one for new data. I fill the first dataset with the
records from MS Access, the second...
|
by: Lauren Quantrell |
last post by:
Using MS Access 2K, I have a client with a number of seperate customer
tables for each country, approx 50 tables, stored on a SQL Server
backend.
I cleaned up the data in the tables and inserted...
|
by: dan |
last post by:
within a loop i am building a sql insert statement to run against my
(programatically created) mdb. it works but it seems unreasonably SLOW!
Sorry, dont have the code here but the jist is very...
|
by: lupo666 |
last post by:
Hi everybody, this time I have three problems driving me nuts :-(((
(1)
I have a report with 20 or so Yes/No "squares". Is there a way to
either hide/show the "square" or change the yes/no...
| |
by: dos360 |
last post by:
Hello,
I have two tables, one is a list of activities, the other a list of
participants. I want to insert one record in the activities table and
then using its identity column as foreign key, I...
|
by: ashes |
last post by:
Hi,
I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net
When someone wants to register on the website, they fill out a form and the...
|
by: AlexanderDeLarge |
last post by:
Hi!
I got a problem that's driving me crazy and I'm desperately in need of help.
I'll explain my scenario:
I'm doing a database driven site for a band, I got these tables for their discography...
|
by: hakkatil |
last post by:
Hi to all,
I have a page that inserts excel sheet to access database. I am using asp. What I want to do is to check the inserting record if it is in the database. Basicly checking the dublicate...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |