Hi,
I need help with following:
Currently i have a browse and read file procedure where the user can select files which are read into a table. Each file contains 14 columns but after read into table each table contains 15 fields. I want field #15 to contain an integer value which identifies each file type that is read into the table.
How would i go on doing that and how and where do i implement that into my code? (see below)
I made a 'try' which are the commented rows but those doesnt work unfortunatly. (notice im new to vba). Name of table is parameters and name of field #15 is FileType.
Really need help and appreciate any help i can get, thanks. - Option Compare Database
-
-
Private Sub Command0_Click()
-
If MsgBox("This will open the folder for imports. Continue?", vbYesNoCancel) = vbYes Then
-
Dim i As Integer
-
Dim tblStr As String
-
Dim varItem As Variant
-
Dim specname As String
-
Dim mySQL As String
-
Dim aob As AccessObject, obj As Object
-
-
i = 1
-
tblStr = ""
-
specname = "Import Specs"
-
Set obj = CurrentData.AllTables
-
-
With Application.FileDialog(msoFileDialogFilePicker)
-
With .Filters
-
.Clear
-
.Add "All Files", "*.*"
-
End With
-
-
.AllowMultiSelect = True
-
.InitialFileName = "C:\Users\Database\Readlog"
-
.InitialView = msoFileDialogViewDetails
-
If .Show Then
-
For Each varItem In .SelectedItems
-
'Shell ("C:\Users\Database\Readlog\readlog.exe C:\Users\Database\Readlog\varItem")
-
For i = 1 To Len(varItem)
-
If IsNumeric(Mid(CStr(varItem), i, 1)) Then
-
tblStr = tblStr & Mid(CStr(varItem), i, 1)
-
End If
-
Next i
-
If Right(CStr(varItem), 4) = ".txt" Then
-
DoCmd.TransferText acImportDelim, specname, "parameters", CStr(varItem), True
-
i = i + 1
-
DoCmd.OpenTable "parameters", acViewNormal, acReadOnly
-
DoCmd.Close
-
tblStr = ""
-
End If
-
-
'For Each aob In obj
-
'If Right(CStr(varItem), 7) = "D02.txt" And Left(aob.Name, 10) = "parameters" Then
-
'mySQL = "INSERT INTO Parameters ((FileType) VALUES (0));"
-
'DoCmd.RunSQL mySQL
-
'End If
-
'Next
-
Next varItem
-
-
MsgBox "Data Transferred Successfully!"
-
DoCmd.Close
-
End If
-
End With
-
End If
-
End Sub
5 2262 NeoPa 32,556
Recognized Expert Moderator MVP Higgs:
Each file contains 14 columns but after read into table each table contains 15 fields.
Are you sure? How does that happen?
I appreciate you're new to code, but your explanation of what you want isn't clear. I appreciate it's not easy to explain, but please at least check all your details. A little work I know, but saves much wasted time for those trying to help, and everybody can do it (It takes no experience).
With so little I can rely on, what I will say is that you will probably need to add the field (FileType) in yourself.
At the end of the code that actually imports the individual files, so within the loop that handles all the files (Pretty well where your commented code is actually, at line #42), you need to add a bit of code to execute some SQL of a form similar to : - UPDATE [Parameters]
-
SET [FileType]=0
As I said, your explanation's not too clear so I'm assuming that the data for each file is in [Parameters] on its own. If that's not the case and the data from all files is loaded into the table together in one amorphous gloop, then you will need to add an extra line to your SQL :
Thanks for ur reply NeoPa.
Sorry if expressing a bit unclear. I'll try explain more in detail what i mean;
The actual files that are imported are txt files containing 14 columns (or fields as named in access). What ive done is to define the 'specname' in the DoCmd transferText so that the table that all the data will be imported into is setup with 15 fields, where ive named all the fields depending on parameter names in the text file and where field #15 is the 'FileType' field. Ive also named the table to 'parameters'.
The reason for adding the 'FileType' field is because i want to be able to 'tag' each file. Each file that is read in contains different types of data and therefore i want the FileType field to contain either 0,1,2, or 3 so that i later on more easily can select the data that im interested in.
The text files can be selected as multiples so everything is read in at once into the table 'parameters'. So as a first step i need to add this integer value into file type field.
Im trying to learn SQL but it takes some time to get used to. Since text files will be added continously to the database there will be the case where the File Type field already contains an integer value (from previously added files). So do i need some type of loop in order to check where the first 'empty' row is? Or is it enough with the UPDATE, SET and WHERE statements which uve posted above?
Do i need to define any recordsets? or objects?
Hope ive explained more clearly,
appreciate ur help very much,
Thanks
i managed to get it to work,
added - CurrentDb.Execute "UPDATE TableData SET BananaField = 0 WHERE BananaField Is Null;"
to the code which seemed to do the trick =)
I still got one more thing i would need to fix though, maybe u can help me;
One of the columns in the data file contains an absolute time which meassures the time from when the data logging starts until it ends. This means that every file has its absolute time column which is unique to the file meaning that the time intervals cannot intersect in between different files.
What i want to do is when reading the files into the table i would like the files to be structured or organized by time so that the one with 'lowest' time ends up on top in the table and so on. All data is structured by lowest time and up. I assume i need to keep track of the first record in each time field and compare that one with the first record in the time field already within the table in database. Then depending on if bigger or not its added either at the end of the field in the database,on top, or in the middle.
Any ideas about how to do that or how to implement it into my code?
Thanks heaps
NeoPa 32,556
Recognized Expert Moderator MVP
That is better Higgs. Much of the info was repeated, but some extra info is helpful and there were no (big) errors due to missing letters or words. That said, we do frown on abbreviated text on here (such as ur for you're etc). Most of our experts find it irritating and disrespectful. You weren't to know that of course, unless you'd read through all the rules (Not a bad idea but we do appreciate that most of our members don't when they first join up. You have more immediate and pressing concerns at that time generally with a difficult problem to solve). So, in all, much better :-)
Back to the question.
I will assume, from what you say (and you can disabuse me if necessary), that the trick of creating the extra field by setting it up in the Import Spec is one that works (I don't think I've ever tried that myself). If so, nice thinking.
In such a case, all you need to ensure is that you run the SQL expression (The one that includes the WHERE clause) at the point where all the records of the specific type have been imported. It will also work if you run it for every imported file of course, but that will possibly involve it being run more often to do the job that can otherwise be done only the once.
There need be no further objects set up as far as I can see, especially if you are happy to use SQL as your earlier post indicated. The code to set up the SQL within the loop could look very much like : - mySQL = "UPDATE [Parameters] " & _
-
"SET [FileType]=%V " & _
-
"WHERE [FileType] Is Null"
-
mySQL = Replace(mySQL, "%V", 0)
NeoPa 32,556
Recognized Expert Moderator MVP
Generally, we insist on separate questions being posted in their own threads. As this is such a simple one though I'll deal with it quickly here.
Databases don't store data in order as such. Storing and processing of data within RDBMSs is done using indexes. There may be many indexes for each table, but the order that the data is actually fed in is irrelevant and certainly cannot be relied upon to indicate the order it is stored in. That said, simply indexing the table on the time field can give you access to the data promptly in that order.
I hope that's clear. If not we will need to split this question away into a separate thread (I'd do that for you this time if it became necessary).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Fawke101 |
last post by:
Hi there,
I am new to ASP and i have really only been exposed to getting data from a
SQL database and displaying it.
I want to include a page in my application (like a form in Access i guess)...
|
by: pmud |
last post by:
I have an ASP.NET web application using C# code. I am trying to insert values
from a web form into an SQL database. I am using SQL COMMAND object for this.
I need to know HOW TO INSERT THE RADIO...
|
by: Little PussyCat |
last post by:
Hello,
I have had a request, one of our tables is used as a report and I have been
asked that all fieldnames for months have dashes in them,
like Jan-05 instead of Jan05 and so on...
Now what...
|
by: Sandy |
last post by:
I have four related tables. I need to do an insert on three of them. I
created a stored procedure that handles the insert on two of the tables
correctly . . . then I get to a tough part.
The...
|
by: tester123 |
last post by:
Can you please help me how to insert values into an exsting table for a single column. Table contains 112 records. I just want o inserert values from 1 to 112 in that perticular column
|
by: ram achar |
last post by:
please send me
C# code for inserting values into database sql server2005
*** Sent via Developersdex http://www.developersdex.com ***
|
by: wenggarcia |
last post by:
please help me..
kindly see if what's wrong with my code below.. im trying to insert values in a table on my database but the query returned as failed.
html code -my input page
<form...
|
by: Bunty |
last post by:
I am not beginer of this language. I've worked on this language for 2
or 3 months.But now i have a problem to inserting values in the
database.It gives me error that DATABASE NOT SELECTED.
I am...
|
by: i12know |
last post by:
Hi guys,
I had some problem inserting values a structure array in C++.
I had define for example
struct UNITS {
unsigned char VariableA;
unsigned char VariableB;
// the...
|
by: arvindmishra |
last post by:
i m inserting values in access database it displays message value is inserted but value is not ptresent in database.
I m using following code
OleDbConnection cnn = new OleDbConnection();
...
|
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: 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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |