General database - overview needed

[CuriousGeorge]

Hi,

Im new to databases and so far ive only made a simple database with help from menus in access with help from an example in handbook (customers contacts etc).

What i want to do is to create a database where i can store datafiles which contains decimal numbers. Each file has 14 columns and approx. 25-30000 rows. Every new file should be stored on the next empty row etc. THe files are currently in binary form which ive a c program which is converting to decimals. So this code has also to be read in, in some way.I also want to be able to do some operations on these datafiles allthough i havent specified those yet.

My question is; how do i approach this? Is it possible to do it only by using access program and its menus? Or do i write code on my own, i.e c-coding+sql-coding which communicates with access? Ive been trying to google to see examples where there are code with both c-code,sql-code and database. I dont see how everything is working together. Do i first write my code and then create the menu-system in access (UI)?

LOts of questions, hope anyone can help.

Thank u

 

[Lightwave]

Curious first off welcome to the Forum

From your outline I would give the following advice. (My version Access 2003)

Yes everything should be possible in Access alone.
You really don't need to worry about c everything should be possible in VBA with maybe some need for SQL if you need to get into particularly complicated manipulation of the data even then you'll probably get away without it.

Think about storing links to the files initially the files themselves seem next to useless as they require manipulation so first stage will be to record the files so that you can batch process the data within them after the information within them has been imported.

So the first thing I would do is record where all the files are.

This can be done in a number of ways. The simplest can be as follows
Make a standard name for all your files and put them all in a single directory.
Make up a table that records each file and create a field to record all the filenames. Now you are going to have a new record for each individual file. And the filename field will contain the filename. It may be possible to import all these names in a oner and simply place in the filename field in the table.

Right next note down the single directory name that you have placed all these files within now look at the following code and replace c:\ below with the exact directory that you have subjectively put everything in.

The following code should be put behind a button on the record and is designed when triggered to open up your file in an appropriate application. So if this was excel excel would open up and you would see your file.

Dim File As String
File = Me.Filename
Application.FollowHyperlink "c:\" & File

Right anytime you hit the button your file should appear.

If you are wanting to manipulate the information within the file I would recommend you have an import routine that takes the files and locations from the above tables. Imports them using something similar to AJTrumpets routine

see this link
http://www.access-programmers.co.uk/forums/showthread.php?t=158308&highlight=Files

Hopefully you have a single table now with maybe one field of highly condensed information.

You now need to parse and manipulate that information as appropriate. This will be done using VBA and possibly SQL. This tends to be reasonably easy although quite involved.

All possible but quite a bit of work

To give you an example of a system I have built
Athletics timing race

The timing boxes produce a text file of hexidecimal keys.
Each Hexidecimal records the competitorrace number date and time of day
The boxes periodically download these to a csv file
Access periodically imports these
Queries are performed on the imported information parsing it and changing it from a single hex string to separate columns of race number date and time.
Queries are run to cross tabulate this information matching it up to the competitors name and cross tabulating multiple times to give start and finish and way point times as required and overall time is calculated from this.

 

[CuriousGeorge]

Hi Lightwave, thank you very much for your response, i really appreciate it.

Reading your post im realising my knowledge 'bout databases might be a bit narrow since ive only made a simple version so far with help from a handbook.

Hope its allright to ask a few questions on the following quote (might be some language barriers here since english is not my native lang):

Make a standard name for all your files and put them all in a single directory. Make up a table that records each file and create a field to record all the filenames. Now you are going to have a new record for each individual file. And the filename field will contain the filename. It may be possible to import all these names in a oner and simply place in the filename field in the table.

- What is meant by making a standard name for all files?
Files will be added continously in the directory as data is collected. The binary files will have names like 200000.D01 200001.D02 etc. Would it be possible to (from the database interface) to just browse the file i.e 200000.D01 and when reading that into the database some kind of script is run using my c-code to convert the binary data to decimals? Is that what you mean with the code that u've added?

- How do i add code to a button?

- About recording the files; How do i record a file? will this result in having one file name for each column? From what ive read about access2007 (which im using) theres a maximum of 255 columns which means that i wont be able to store more than 255 files?

Lots of questions again and i understand it might be frustrating explaining something which might seem obvious. As mentioned earlier the book i got is just showing the basics and not really anything more advanced about how creating tables/command buttons etc and adding code to them and how everything is working (interacting) together. Do u know where i can learn/read more about that? Is it just 'learning by doing' and head to the forum when having questions? =)

Thanks again

 

[Lightwave]

Hi Curious I'm slightly busy at work at the moment and so don't quite have time to give you a complete explanation at the moment.

The following points immediately come to mind.

Don't worry about the 255 columns you will be adding each file as a record to a single table so you have a massive number of files you can store. There is a limit on field names but you will be adding records and not fieldnames everytime a new file is recorded.

Changing from binary to decimal is a separate thing and the table of file names will just be used as a reference so the code knows what files to import. By adding the files to a table you make the code much more flexible. Otherwise you'd have to list the individual filenames in the code... Messy.

The naming convention of the files looks like its standard already. Ie each name has the same length and follows the standard format with an increment I think from the two listed. (much like any sequence)

Code behind a button is a case of drawing a button on the form and then right clicking on the button and going to the properties then going down to the property eg Click event and selecting tiny button to right with three dots. In Access 2003 this takes you to the visual basic editor. This is where you can put all your code "behind" the button.

This will be quite a difficult process so you will have to break everything down to its constituent parts, if you don't tell your database exactly what to do it won't do it.

Working at the moment but should have time over next few days so if its ok check back on Thursday evening and will try and give you a fuller explanation.

 

[smig]

@CuriousGeorge:
you should not store the file in the db, only it's name.
unless you want to import the data from the files into new tables in Access.

 

[CuriousGeorge]

Thanks Lightwave, appreciate it. Ill try to figure some stuff out meanwhile.

Cheers!

Also thanks Smig for ur input.

 

[Lightwave]

Curious did you start to figure things out in the meantime??

I hadn't forgotten just got tied up with other stuff.

Its quite a complicated thing to do so any more comments are likely to be general unless you can give me a bit more description about how far you've got so far.

 

[CuriousGeorge]

Hi Lightwave, thanks for gettin back to me.

Yeah i do have some questions I've only been doing some reading, got hold of a pretty good book which helped a bit and clarified some stuff.

If we just focus on the reading files part;

If i make up a table where each record contains a file name - do u mean "by storing as linked file" that i should store them as "attachments"? "attachments" is a way to store the type in 2007, just as storing it as a "number" or smth. If storing as an attachment im storing all the filenames in the same record.

But if saving all the files as linked files where i choose all the files from a directory...how is that automatized? As i interpret ur explanation u want me to add each file to the table, i want the user to add the files from the directory by himself and then everything to be read in.

I find it difficult to understand this part of reading the files into the database. I'll just try to clarify it again so there arent any misunderstandings :

The data files are binary files with different filenames ending with *.D02 *.D03 etc. These files are continously stored in some directory on the computer meaning that new files will be added as time goes by. From the 'form' point of view i want the user to able to upload these files into the database by browsing the directory and selecting the files. When doing so i have c-code that converts the binary format to normal decimals which i want to be used so that the file that the user is uploading will be stored in decimal format.

There are of course more stuff i want to do but it might be good to start here and not trying to do to much stuff at the same time

I'd really appreciate if you could help me a bit with this part.

Thanks

 

[Lightwave]

Hi Curious..

You are not going to be "Attaching" your files to the database.

You will simply be storing a list of locations of where the files are.
So you will have a list of the files and the filename and location eg

c:\users\curiousgeorge\file1.d001
c:\users\curiousgeorge\file2.d001
c:\users\curiousgeorge\file3.d001

There will be a new record for each file. The files will be picked by the users but those selections will be stored in one field of a table.

Yes you will be able to get the users to use a file dialog box to enter this information into the database. But you are not attaching the file you are simply storing a text string that records the filename and location.

Once the user is happy with the selections THEN they can hit an import buttn.

My suggestion is that the separate button IMPORTS the infomration from these files previously selected and places that information within a separate table. This will be the raw information which you can then convert from binary.

The list of files are only required because within the code the computers needs to work through the files and import them consecutively so this is when you need the locations in table. If you didn't have the locations it would only be possible to import if the file was hard written into the code. If you have a list that list can be short or long but you can create a code loop that looks to the table and imports every file within the table.

So the code would be something like
Import binary file into table 002
Look at table 001.filenamefield
Import
Move to next file
Back to Import
Continue until reach end of table.

Curious please take another look at this link below it gives an example of importing multiple files the only difference is that you are wanting to import binary files rather than excel files. The code you use will almost be identical to the example database.

http://www.access-programmers.co.uk/forums/showthread.php?t=158308&highlight=Files

You will note that it allows the users to pick files and then subsequently imports them afterwards.

Think of it like this. If I asked you to manually open a series of excel files and then convert them from one format to the other format what would your first question be..

Please tell me the name and location of all the files you want changed??

 

[CuriousGeorge]

Thanks alot Lightwave, ill have a go at it!

The link u posted didnt work though, seems to be some letters missing =)

Cheers

 

[Lightwave]

I'll go back and recorrect the previous one as well but here it is again for completeness.

http://www.access-programmers.co.uk/forums/showthread.php?t=158308&highlight=Files

 

[Lightwave]

As someone that is new to databases this is a tricky task so it will probably take a bit of study. Afraid there's no getting round that.

 

[CuriousGeorge]

hi again,

im trying to use the code that u pasted in the beginning of the thread for the original files with endings .*D00 etc:

Option Compare Database
Option Explicit
 
Private Sub LinkFile_Click()
Dim File As String
File = Me.FileName
Application.FollowHyperlink "C:\Users\SSS787\ReadLog_new" & File
End Sub

This is giving me the following err msg: 'method or data member not found '

Ive been searching for browsing code but those examples are including code which contains 100+ lines which seems unreasonable large amount of code just to perform a simple task like this.

Why aint this code working?

Cheers

 

[Lightwave]

I think you need a \ to be placed after the ReadLog_new so it should read

Application.FollowHyperlink "C:\Users\SSS787\ReadLog_new\" & File

rather than

Application.FollowHyperlink "C:\Users\SSS787\ReadLog_new" & File

If that doesn't work the next thing to check would be when in explorer if you click one of the binary files does it open ok or is an error message produced. It may be that you don't have any programs that recognise that type of file (although I'd expect you'd get a different error message from that_

And yes using the file dialog box does use greater amounts of code than some beginners in Access are comfortable with.

 

[CuriousGeorge]

Hey,

Yeah same error msg occur after adding \. Its highlighting the '.FileName' part in the code.

When trying opening the file in explorer windows is unable to find a program suitable opening the file with. 'Forcing' it to open with noteped displays a txt file with columns but every character is like wing ding characters.

How come that its not possible to just store the file path as a string regardless of filetype? Is Access assuming manipulation as soon as its added to a record and therefore it has to be a valid one?

appreciate ur help,

thanks

 

[Lightwave]

OK CG -

Access is storing the file as a text however you don't have a program on your computer that is able to interpret the information in the file. So when it trys to open it it errors out because Windows doesn't know which program to use to open it.

This suggests that it is an unusual type of file.

1.Are the files live feeds from some device?
2.Did that device come with any software associated with it?

I know you said that the files are binary but have you seen the actual
binary structure of the files and do you know how to parse the information in a detailed manner to convert it to decimal?

I think you mentioned that the files have a different extention every time

ie
*.d01
*.d02

I should have spotted that - the extention of a file tells windows what program should be used to open it. As far as I'm aware these are fixed according to the program. So access uses mdb, notepad txt excel xls etc... So the fact that they change with each new files is pretty unusual.

It looks to me as if you are going to have to do pre-processing on the binary files before you can start thinking about importing them into access. The software that came with the device will probably key to this see 2.

 

[CuriousGeorge]

In the specified directory there are also text files which just as well could be selected so i dont understand what it is in that code that is detecting the file type since its not even compiling?

---------

The software is built in, in the device that is generating the parameters.

There is an application .exe which converts the binary data to normal data by using the c-code.

In matlab theres a command like this:
!ReadLog.exe 00000041.D00 > slask.txt;

which calls for the program to execute the c-code and create a txtfile.

If there would be a way to do a similar thing in access using VB code to just call this application and generate text files and from that load those into tables might be an alternative way?

The question is, how is that done in access?

 

[Lightwave]

In the specified directory there are also text files which just as well could be selected so i dont understand what it is in that code that is detecting the file type since its not even compiling?

OK CG

The code doesn't detect the file type. Windows looks to the suffix extension and from that determines which program should be used to open the information. If it comes across an extension it doesn't recognise it throws an error. That's probably what's happening to your machine. If and when it were opened it would only open it in another program this would not import it into access. Importing is a whole different thing.

It is beyond my knowledge of how to get Access to control MatLAB

From my investigations on the web it would appear that it is possilble to multiple file importing within MATLab and it does look like you can get it to do work on those files in some shape or form.

see this link

http://www.mathworks.com/support/solutions/en/data/1-190XP/index.html?solution=1-190XP

It may be possible to do this from Access but I'm afraid I don't know how to do that.

It is likely that Access will be able to manipulate the txt files once matlab has processed them but there is still quite a bit of work.

If I was in your position I would look to doing batch processing of the binary files completely separate to Access and then look at a database that may take the txt files from there.

Have you thought about hiring outside help for this one?

 

[CuriousGeorge]

Yeah, i got a meeting scheduled next week with a guru at my uni. Really want to integrate the whole reading part into the database and there just have to be a way of doing that

Thanks anyway!

 

[CuriousGeorge]

Hey again,

Im lookin at the code u pasted before regarding reading multiple files. Is it possible to get some help understanding what that code does?

This is a part of it:

For Each varItem In .SelectedItems
                        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 acImport, , tblStr, CStr(varItem), True
                            i = i + 1
                              DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
                                MsgBox "Data Transferred Successfully!"
                                  DoCmd.Close
                                    tblStr = ""
                        End If
                      Next varItem
                    DoCmd.Close
                    End If

I guess selecteditems are the total files selected and that varItem is each file?

But lookin at the first loop: len(varItem) is returning the number of characters of first selected file? So why are we checking isNumeric then in the if statement?

I dont get the idea..are we really checking the file names? Isnt the content whats interesting?

Also "next i" i guess we go back up top to the inner loop all over again until reaching len(varItem)? But why is i=i+1 performed below then?

i mean in that case when arriving to

If Right(CStr(varItem), 4) = ".txt" Then

i will always be len(varItem)?

Where in the code do we decide to put everything in one column?

Ive tried reading a text file containing a couple of columns but everthing is padded into one.

Cheers