Connect Program to Database Backend

dynamix

Registered User.
Local time
Today, 00:52
Joined
Feb 9, 2005
Messages
38
Hey guys... I created a reaaaally simple wages calculation program some time ago in VB6. When you click the save button, the records are saved to a .txt file stored in the same directoy as the program itself.

However, I'd like to take this a step further and connect this program with some tables setup in an Access database. I know this has something to do with ADO, right? But not quite sure how to set this up..

Would it be possible to get some guidance on this? If you'd like to see the program, let me know.

Thanks for any input!
 
Think you need to explain this a little better in order for people to offer advice.
 
dynamix said:
However, I'd like to take this a step further and connect this program with some tables setup in an Access database. I know this has something to do with ADO, right? But not quite sure how to set this up..

I'm certainly not an expert in DAO or ADO, but I would suggest to make linked tables in your Access Database. Therewith you provide yourself with a link from Access to these stored .txt files in your calculation program.
 
Jibbadiah said:
Think you need to explain this a little better in order for people to offer advice.
I don't think it's possible to explain it anymore clearly.. It's a pretty straight-forward question.. I thought..

When the data is saved, instead of saving to the .txt file, I want the data to save to sme predefined tables in a database backend.

I wanna drop the .txt file completely and just have the data stored in tables.

I do not know how to explain it any clearer :(..
 
Why don't you save the .txt files in some dedicated folder and automate the import to the Access Dbase from that folder.
 
I agree with rak... failing anything else, it is easy to automatically import the files and then delete them after use.

You could try something like the following:

Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName", "c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

-- credit to Joe Falon (DBforums)

James
 
Dynamax, you have 1 of 2 choices:
1. Link your text file to access so that anything you save in the text file is automatically reflected in access via the link. This will be the easiest and quickest way to make your data available in access.

2. Write code on the application so that it saves in to access. You will need to create a connection to your database and perform an insert in to the table. The snippet below perfomrs a select statment, the insert will be similar except that you will do "Insert in to sometable, field name, values something".

Dim Conn, rsRecords
ConString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & Path to your server & ";" & "JET OLEDB:Database Password=yourdatabasepassword"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString
Set rsRecords = Conn.Execute("SELECT * FROM Yourtable Order by Somefield")
While Not rsRecords.EOF
 
Thanks for your reply.

1. How would I go about your option 1?

2. Just looking at the option 2 you gave me.. I do not have a server and I am not hosting this online.. It's just for a school project.

Thanks again!
 
Go to the tables tab in Access.
right click
choose link tables
Files of Type = Text
Browse for the file you want to link to.
 
Jibbadiah said:
I agree with rak... failing anything else, it is easy to automatically import the files and then delete them after use.

You could try something like the following:

Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName", "c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

-- credit to Joe Falon (DBforums)

James

Okay, so I'm a real rookie with VB and well.. Access too..

So I need it in Laymens (if ou can go anymore 'laymens' than you already have?)..



To give you some idea what I've done, here goes:
-----I added that code you gave me (above) and then created a database with one table and all the required fields..

Now all I need is so when someone clicks the 'Save' button in the program, all the details get saved to the table in the DB.

I edited your code to match my requirements and it now looks like so:
Code:
Public Sub SaveAstxt()
'Save to .txt File

Dim strfile As String

ChDir ("C:\Documents and Settings\Owner\My Documents\College Work\Unit 4 - NEW\Program Files")
strfile = Dir("SavePayroll.txt*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "dbBackend", "tblWages", "C:\Documents and Settings\Owner\My Documents\College Work\Unit 4 - NEW\Program Files" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "C:\Documents and Settings\Owner\My Documents\College Work\Unit 4 - NEW\Program Files\SavePayroll.txt" & strfile
strfile = Dir
Loop

End Sub

Load frmSavedDoc
frmSavedDoc.Visible = True

End Sub

Needless to say, it doesn't work :(.

Any more help guys?
 
Jibbadiah said:
Go to the tables tab in Access.
right click
choose link tables
Files of Type = Text
Browse for the file you want to link to.
Sweet, it worked :O!

One question.. With the importing of the text file, the formatting has gone all crazy and the fields/columns are absent (everything is being crammed into the one column (see screenshot attached). How would I go about sorting the fields out etc? See screenshot attached..

Thanks again, you have been awesome!
 

Attachments

  • crazytable.gif
    crazytable.gif
    8.3 KB · Views: 154
When you first click link in the tables tab - a link text wizard will pop-up that allows you to choose how you want the file linked. It will give you delimiter options. Choose those that are relevant to you. Provided you do it correctly you shouldn't have any problems. Experiment with the different options available.

As for the previous post, I have simplified the vb. Try this and then amend accordingly. Start simple and build it up. The following code should work first time.

Sub test()

Dim strfile As String

strfile = "c:\temp\yourfilename.txt"
DoCmd.TransferText acImportDelim, "yourimportspec", "table", strfile, True

'Careful - this line will delete your file
kill strfile

End Sub

When you get this working you can try changing the directory. You may need to use the dos names for the path to your file. Something like:
("C:\docume~1\owner\mydocu~1\Docume~1\Colle~1\Unit4-~1\progra~1")
The way to check is to open explorer on each section of the path. Right click, goto properties and check ms dos name.

When you want to check your vb to find errors you click just to the left of "Public Sub SaveAstxt()". This allows you to debug line by line. Now when you run the code you can press F8 to step through each line and it will tell you if something is wrong. If you move the cursor over the lines that have just been run you should see the values of any variables that you have declared (in this case strfile).

James
 

Users who are viewing this thread

Back
Top Bottom