This will require some VBA code to sequentially process the records.
The H and D types indicate to me that you have a master (order?) record and a child (OrderDetails) record that will belong to the master.
You'll need code like: -
' define recordsets for master and detail table
-
dim rsMaster as DAO.recordset 'This requires Tools/References to hold a Microsoft DAO version #.## reference
-
dim rsDetail as DAO.recordset
-
-
dim strLine as string
-
dim strKey as string
-
-
set rsMaster = currentdb.openrecordset("tblMaster")
-
set rsDetail = currentdb.openrecordset("tblDetail")
-
-
open "C:\input\myfile.txt" for input as #1
-
while not eof(1)
-
input #1, strLine
-
if len(strLine) > 1 then
-
select case left(strLine,1)
-
case "H"
-
strKey = mid(strLine,2,10)
-
rsMaster.addnew
-
rsMaster!Keyfield = mid(strLine,2,10)
-
rsMaster!Field1 = mid(strLine,12,10)
-
'... etc
-
rsMaster.Update
-
case "D"
-
rsDetail.addnew
-
rsDetail!Keyfield = strKey
-
rsDetail!Field1 = mid(strLine,12,8)
-
rsDetail!Field2 = mid(strLine,20,5)
-
'... etc
-
rsDetail.Update
-
end select
-
endif
-
wend
-
-
It's "aircode", but I hope it gives the idea how to process the data.
Each row not starting with H or D will be ignored and the H will define the key for the D.
Nic;o)
I have question similar to the above:
I want a code that will import text, excel, mdb files from a specified drive in to an existing or new Access table. Any suggestions will be appeciated.
Here is my code: - Private Sub cmdStartImport_Click()
-
-
Dim rst As ADODB.Recordset
-
Dim strNewTable As String
-
Dim strFileType As String
-
-
-
-
On Error GoTo ErrorHandler
-
-
'Check if the file name was entered'
-
If IsNull(Me.selFileName) Then
-
MsgBox "You must enter the path and file name.", vbEclamation, "Error"
-
'Me.File.SetFocus
-
'GoTo StartImportBtn_Exit
-
End If
-
-
-
'If new table was checked, verify tablename entered
-
If Me.chkNewTable = True And IsNull(Me.txtTableName) Then
-
MsgBox "You must enter the new file name.", vbExlamation, "Error"
-
Me.txtTableName.SetFocus
-
-
End If
-
-
'Determine what information to import
-
Select Case Me.txtPath
-
Case 1
-
strExistingTableName = "tbl_Name_0122"
-
Case 2
-
strExistingTableName = " tbl_Name1"
-
Case Else
-
MsgBox "You must enter the new file name.” vbExlamation, "Error"
-
Me.txtPath.SetFocus
-
End Select
-
'if new table, then set the variable for the new table name
-
If Me.chkNewTable = True Then
-
strNewTableName = Me.TableName
-
Else
-
strNewTableName = "tbl_Temp"
-
On Error Resume Next
-
DoCmd.DeleteObject acTable, "tbl_Temp"
-
On Error GoTo ErrorHandler
-
-
-
End If
-
-
'Determine the type of file being imported by determining the last file extension"
-
-
strFileType = Right$(Me.selFileName, 3)
-
-
Select Case strFileType
-
-
Case "mdb" 'Access Database
-
If Me.chkNewTable = True Then
-
DoCmd.TransferDatabase acImport, "Microsoft Access", Me.selFileName, acTable, strExistingTableName, strNewTableName
-
-
-
Case "txt" ', "csv" ' Check for text files
-
DoCmd.TransferText acImport, acImportDelim, strNewTableName, Me.selFileName, True
-
Case Else
-
MsgBox "Please enter a correct file type, choose *.mdb, *.xls, *.txt or *.csv.", vbExlamation, "Error"
-
-
GoTo Done
-
End Select
-
‘If new table was selected, then done, else append data to existing table
-
If Me.chkNewTable = False Then
-
-
' Run append query from temp table to new table
-
DoCmd.RunSQL "INSERT INTO" & _
-
strExistingTableName & _
-
"SELECT tblTemp" & _
-
"FROM tbl_Temp"
-
DoCmd.DeleteObject acTable, "tbl_Temp"
-
End If
-
-
'Display the Import is Completed"
-
MsgBox "Import Successfully Completed.", vbExclamation, "Import Status"
-
GoTo Done
-
-
-
-
Error Handler:
-
MsgBox Err.Description
3 5360 NeoPa 32,556
Expert Mod 16PB
Firstly I've split this into a new thread as it was not directly related to the other thread so was essentially a hijack.
As to your question :
If you look in ( POSTING GUIDELINES: Please read carefully before posting to a forum) you should see what is necessary to include in a question. I'm sorry but this is not a simple code fixing service. We can respond to specific questions and we have many very helpful and friendly experts who will happily do that for you.
You need to ask a straightforward question for that though. If your code isn't working for you then you need to say exactly what you are looking for and specify what error message you are receiving and whereabouts in the code.
That's a bare minimum.
MODERATOR.
Firstly I've split this into a new thread as it was not directly related to the other thread so was essentially a hijack.
As to your question :
If you look in (POSTING GUIDELINES: Please read carefully before posting to a forum) you should see what is necessary to include in a question. I'm sorry but this is not a simple code fixing service. We can respond to specific questions and we have many very helpful and friendly experts who will happily do that for you.
You need to ask a straightforward question for that though. If your code isn't working for you then you need to say exactly what you are looking for and specify what error message you are receiving and whereabouts in the code.
That's a bare minimum.
MODERATOR.
Thanks for your advice> As I mentioned in my introductory statement, I posted this question before I saw the guidelines.
NeoPa 32,556
Expert Mod 16PB
Now I'm thoroughly confused.
There's still no attempt to rephrase the question (in this thread) so I'll lock it unless and until I hear from you via PM that you would like to continue with it and are ready to post a question that follows the guidelines (I'll assume that you've now read them - clearly my last assumption after directing you to them in an earlier thread was premature).
Maybe the introductory statement made things clearer but I was unable to find anything that fits your description.
MODERATOR.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Doug Baroter |
last post by:
Hi,
One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in...
|
by: Jon Ole Hedne |
last post by:
My Access 2002-application need to work with tables from both Oracle and
Access. To solve this, I want to run some querys on three views in Oracle
and import the results into temporary...
|
by: DCM Fan |
last post by:
Access 2K, SP3 on Windows 2K, SP4
All,
I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no...
|
by: Dan |
last post by:
Could someone please help me with auto importing a series of data
files into an Access table. I tried to follow code given below in a
previous messagebut i'm getting error messages.
Here's my...
|
by: shumaker |
last post by:
I don't need a detailed description of a solution(although I wouldn't
mind), but I am hoping someone could tell me in general the best path
to go about accomplishing a task, since I don't know all...
|
by: David Berry |
last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the...
|
by: NewbieSupreme |
last post by:
I'm using PHPMyAdmin on an Apache2Triad install (latest version; 5.x.x,
which installs PHP5 and PHPMyAdmin 2.8 as well). In Access, I exported a
table to a text file, tab-delimited, text qualifyer...
|
by: Earl Anderson |
last post by:
I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt' formatted data
into AXP, I got a "Type...
|
by: a |
last post by:
Dear friends
I want import data from CSV file to mdb file How can I do that in vb.net?
|
by: provor |
last post by:
Hello,
I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |