inserting values into table

[Higgs]

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

[NeoPa]

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 :

WHERE [FileType] Is Null

[Higgs]

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

[Higgs]

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]

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]

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).