[Resolved] Need help with database

[super_nOOb]

Hey guys,
Im glad i could join the forum
I have a doubt here, maybe you can help me out

Im setting up the database for my project. What i actually needed to do is to create a database (.mdf) where each table of the database would be a .txt file located at my folder G:\xTemp\txt.NET. This folder is constantly being updated and i wanted that my database could, after i set it up, get all updates too.

So basically:
1) How could i automatically populate my .mdf database with my .txt files located at G:\xTemp\txt.NET where each table would have the same name as the .txt file

2) How can i manage to automatically update the database

I really hope you guys could give me some light here

Thx in advance for the replies

[josep]

Hi and welcome

Im setting up the database for my project. What i actually needed to do is to create a database (.mdf) where each table of the database would be a .txt file located at my folder G:\xTemp\txt.NET. This folder is constantly being updated and i wanted that my database could, after i set it up, get all updates too.

Why do you need that?, instead of that you can update directly the database using .net tools; it will be easy and 100% done in realtime

hope this helps

[super_nOOb]

Really....I thought this would be the easiest way. Because the .txt files are some time series i get from the actual program the company is using now. And basically they are updated everyday. So i thought it would be easier to create some command that would do this all for me.

How can i do that using the .net tools?

By the way thanks a lot for the reply

[josep]

Then If the program will be used after your development that's fine, you will need to update de database with this txt files you get from the existing program

Population of your database will be done once in live or on a daily basis?
you will use the existing program once you finish your development?

As start, check on these links for ADO.NET (you will need to interact with the database)

http://www.developerkb.com/modules/wfsection/article.php?articleid=52
http://www.developerkb.com/modules/w...p?articleid=58

[super_nOOb]

Awesome

thanks a lot, ill check those links, but basically the company will be runing both programs. this one im making is just a "support" program where ill develop a few reports. But the main program will still be running. So basically ill need to update the .txt files into the new one at the daily basis. The idea was to set up the database with the data i have from 2002 until today and then try to automatically update it in a daily basis

[josep]

By the way, The existing program should work with some database, so maybe is better to use this db to get the data (if business allows you) instead of doing imports of data from a txt file, that will be bigger each day, and you will need to do a lot of checks inside your code to update rows from yesterday changed data and insert new ones.

If it's not possible, maybe you can use 'vinculated' tables from Access to get the data (that's nothing to do with VB, is just Access feature) then from VB you can get it from Access.

[super_nOOb]

Oh, i see. Actually i dont have access to the company database. I have to set a few parameters and export the .txt files. But this i managed to do automatically every day. I have quite a few .txt files there are exported into that folder i listed above. But basically my resource available would only be the .txt files. I could try running a macro at Access to creat the database, which i actually tought about. But the main concern i have on that is:
Every day 1 single line with a value will be updated at the .txt file. I was wondering if by making this macro would the whole database be uptaded everyday? I guess it would! So i was trying to find a way to update the lastvalue only. Maybe i could do a loop count and then ask to get last value...........well i dont know............
But i thought it would be better to have all sources running in the same program. Do you think its worthed giving a try at access?
If yes then i should try to link the mdb database to the .net one right?

Thanks a lot for the replies again

[josep]

Access has some limitations, personally I preffer not to use, but sometimes you have no chance
main limitations are:
- not to be used by some users at same time
- All tables are stored on the same file, this file must be under 1GB
- if you do not construct correctly the sql sentences, it will waste your network resources, and the performance of your application will slow down
- use it only as table recipient, so do not use forms, or reports from there

If you can get tables linked to the main DB it will work easily (the interface between both applications) with the limits I told you

If you finally uses access, you will need to use ADO.NET to get the data on your VB application, then use it for your purpose (show on datagrids, filter it, print,...)

I strongly do not recomend to insert or update any linked tables from your application, only perform select queries (you can have triggers or any other thing that updates the DB)

[super_nOOb]

Oh Ok. So basically I should use the Access (.mdb) to "hold" and update the information. All other functions should be treates at .NET after i set it to VB with ADO. I've seen the triggers at VB.Net. I'll see if i can find this functions at Access.

thanks for the replies, and ill be updating how im doing with this project

[super_nOOb]

ummmmmmmm..........I'm not too sure if access will be helpfull because every client has a .txt file. In access i have to creat one macro for each of the existing .txt files. But in case i add a new client i would have to go all the way to the source files of access to add a new macro which i dont think is a good idea for this program, since in the the near future other people will be running it. My ideas was to make something more automatic that actually made one table for each .txt at the folder. So if a new .txt is added it should add another table without having to worry to creat another macro for that

[josep]

Hi

If every user has a txt file on his own machine or on his own personal folder on the server, it will be nightmare to get them on a database, ant it will mean you will have a table for each user, ...

Try to get the data from the database on a linked access (you can have more tables on this database than the ones linked, just for the purpose of your app) or better access to the original db.

regards

[super_nOOb]

Yes, I know it will be a nightmare. I managed to get all .txt file in one folder on the server. But to make a linked access from the database i would have to access the database from the program that is running now, which im pretty sure i wont be able to do. And i still run in the same problem of having to manually create another lionk when another user is created. Which i wanted to make automatic.

So pretty much i cannot find a way out of this T_T

[josep]

Why are you having a new table for each user?

The tables of diferent users are having the same structure?
If yes, can you create a table that contains all data for all users; if then you cannot distinguish between different user data you can add a field that makes it different (username or something like that)

[super_nOOb]

Yes. I could do that too. Maybe its gonna be easier. But do you know how could i run this data into one table and still make it to uptade everyday? Maybe a macro would be my best choice right?

[josep]

yes, You can do it in a macro, but writing macros accesing txt files will not be easy.
If you are using vb.net i think will be easy to do it from there, you will have more debug capabilities and also you will have more featuresto interact with files.

The txt files should have a name, this name is user ralated, or even is no user related, everyday the name must not change for the same user, so you can use this name as username or create a table of usernames <-> filenames.

[super_nOOb]

Yes the name will always be the same for the same user. But when a new user is added ill have another .txt file with his name.

So should a write the macro at a module at vb.net to get the .txt files make each column at the name of the .txt file. Do you have any code that i can start from?

Again josep many many many thx for the replies

[super_nOOb]

I was tring this code

Function ImportTextToAccess() As Boolean

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=C:\Documents and Settings\user\Desktop\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [tbl1] FROM
[Text;DATABASE=G:\xTemp\txt.NET\textfile].[textfile.txt]", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function

[josep]

But when a new user is added ill have another .txt file with his name.

you can get all files on a folder, so problem fixed.

I will try to find something to send you some samples this afternoon

[super_nOOb]

oh ok..........thx a lot

And yes i managed to get all of the files in one folder

[super_nOOb]

The folder where the files are is this:

G:\xTemp\txt.NET\

The files are for example:

a.txt, b.txt,....,z.txt

And the content inside each notepade looks like this:

Date Value
31.12.2001 0.0000
01.01.2002 0.0000
02.01.2002 0.0000
03.01.2002 0.0000
04.01.2002 0.0000
05.01.2002 0.0000
06.01.2002 0.0000
07.01.2002 0.0000
08.01.2002 0.0000
09.01.2002 0.0000
10.01.2002 0.0000
11.01.2002 0.0000
12.01.2002 0.0000
13.01.2002 0.0000

And my Access file is here

C:\Documents and Settings\user1\Desktop\db1.mdb

[super_nOOb]

Ok. 1st problem solved. I managed to link the data to the database. So it is updating live. Now i just needed a code that would see if there is any new .txt files to create them

The code im using now is :

Sub import2()
Dim strfile As String
strfile = "blabla.txt"
'Do While Len(strfile) > 0
DoCmd.TransferText acLinkDelim, "old Link Specification1", strfile, "G:\xTemp\txt.NET\" & strfile, True, ""
'Loop
End Sub

What I actually needed was to transfor the strfile as a variable being the name of the file in the folder. And make a table for it if has no table there with the name yet. How can i do that?

[josep]

Opps,

I did not ask you which VB.Net version are you using
for vb2005 or vb2003 it works

Dim result() As String
        result = System.IO.Directory.GetFiles("G:\xTemp\txt.NET\")

you will get all files of the folder on an array of strings

[super_nOOb]

Im using Microsoft Visual Basic 2005 Express Edition

Ive tried running this code both at .NET and at Access module but it seems not to work. I got error messages in both of them.

Its an error '424' and its says object is required

[josep]

Hi,

it's wortking on my vb2005

Dim result() As String
        result = System.IO.Directory.GetFiles("c:\")

the parameter for get files must be an existing folder otherwise it will prompt an error
set a breakpoint after the getfiles method and check for the values on result (it's an string array with all the files on the specified folder).

[super_nOOb]

Hey Josep,

i dont know why but even the Last code you gave did not work. Do you know if i have another way out?

[josep]

Which error gives to you?

[super_nOOb]

In MS Access i get this: Run-time error '424' Object required

In VB 2005 i get this : Visual Studio cannot start debugging because the debug target 'C:\...\filename.exe' is missing. Please build the project and retry, or set the OutputPath and AssemblyName properties appropriately to point at the correct location for the target assembly.

[josep]

The code I provided is for VB, so it will not work in access.

The error you get on VB seems that something is wrong with the solution.
Just start from zero on a new project; I think it will fix the problem

[super_nOOb]

same thing happened in hte new project. Odd and strange...........Maybe it should be easier to run this vb code at access. i mean let access handle all the database and then i just import the final database at VB.net

Ill try to get a code for access to make this array

[josep]

Do you have any project working on your developing environment? or it is just a new setup of VB? if so maybe reinstalling it again it will work.

[super_nOOb]

I managed to get a code for access. The code is below

Sub GetFiless()
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")
Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir

DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

Loop

' Trim array size to actual number of files
ReDim Preserve Files(1 To nFiles)

'
End Sub

So far the code is looping and making a linked table for each .txt file i have. But when the it reaches the last file it has to end the loop, which by the code it was supposed to do, but its not doing. After i manage this problem i will also have to make an IF statement where if the table already exist it should do nothing.

[super_nOOb]

I think what i need here is:

To make a IF THEN EXIT DO statement when the last file is looped

[josep]

I'm not too familiar with acces code (remember you are in vb.net forum...)
Considering that you must check what are you getting on filename after get the last file, and check for that on the while condition

[super_nOOb]

Ok, Problem solved.
I used this code:

Sub import2()
Dim testmsg As Integer
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")

Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir
If FileName = "" Then Exit Do

DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

Loop

' Trim array size to actual number of files
ReDim Preserve Files(1 To nFiles)


End Sub

Now just need to make another IF table exist in the access tables then go to next loop

[josep]

It's better (the code is clever and later on it will be easy to review)
to not use exit do, just add the needed conditions to the loop condition

[super_nOOb]

do you have any tips on codes for the condition:
If Filename = table from Access THEN go to next loop

[super_nOOb]

It should be something ilke this:

If FileName = Table name from Access
Then go to next loop
Else DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

The bold ones are what i have to find out

[super_nOOb]

ok. Basically i solved almost all of my problems. THe code im using is the following

Function tableexists(tbl As String) As Boolean
On Error GoTo nofile
tableexists = CurrentDb.TableDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
tableexists = False
Resume exithere
End Function



Sub import2()
Dim yourMsg As Integer
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

FileName = Dir(Path & "*.*")

Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
' Get next file
FileName = Dir
If FileName = "" Then Exit Do
If tableexists(FileName) Then Else yourMsg = MsgBox(FileName, 1, "MsgBox")
'DoCmd.TransferText acLinkDelim, "Apollo Link Specification1", FileName, "G:\xTemp\txt.NET\" & FileName, True, ""

Loop

' Trim array size to actual number of files
'ReDim Preserve Files(1 To nFiles)


End Sub

Basically i just need to solve one more thing. The FileName array is giving me myfilename.txt . When the table in Access is created its is automatically named as myfilename_txt. So the code i wrote to see it table exist and to create nem table only if it does not exist is not working. BAsically i would have to set a parameter for FileName to take off the extention.

[super_nOOb]

Ok. I managed the Access Issues

Thanks a lot for the replies JOSEP.....