Solved

Changing Link Path of Access Table (linked to text file) URGENT

Posted on 2003-12-04
31
2,143 Views
Last Modified: 2008-03-10

I am trying to speed up some operations in my database.
I have an Idea I want to test
I want to change the link of a table from one txt file to the next. I can’t seem to figure it out.
I am currently importing the txt file using TextTransfer for each record and deleting it. I think that changing the link will be faster than importing 1000+ records and deleting them each time. Code is as follows. I have named the path with a test name for example. I have this code in a function where it gets the path in order to know what to change the link to.
This is urgent any help is appreciated.
Zack


'for example only
varTestFilePathNew = "\\testing\test\er5-s1.txt"


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = DBEngine.Workspaces(0).Databases(0)
Set tdf = db.TableDefs("tblRawDiePassiveLink")
tdf.Connect = ";DATABASE=" & varTestFilePathNew & "; specDieImportPassiveRev1"
tdf.RefreshLink
MsgBox "" & tdf.SourceTableName & ""

Set db = Nothing
Set tdf = Nothing
0
Comment
Question by:lightcross
  • 13
  • 10
  • 5
31 Comments
 
LVL 15

Expert Comment

by:Raisor
ID: 9879002
Hi,

I started testing arround a little ... but I must admit "I'm not really getting in to what you're up to do"!

Can you describe the tasks you want to perform?

Like:
1. Declare a variable holding a text file name + Why would you want that
2. Create a DAO.Database Object + Why would you want that
3. ...

tdf.connect = ";DATABASE=" & "\\testing\test\er5-s1.txt" ... <- what is that good for? *.txt as flatDB?


Please give some more information about vision and scope!


Best regards, Raisor
0
 
LVL 2

Author Comment

by:lightcross
ID: 9883645
That path is held in a table as a hyperlink. I set it to a variable then trim off the "#" in the beginning and end. Every record in the table has its own hyperlink, which is the raw data for that record. I want to cycle through all the records in the table and re-analyze them. I want to cut the time required and the longest portion to analyze the raw data is importing the txt file.
All text files have the same format
Right now I import the text file containing 16000+ records and analyze the raw data save it into one record and then archive the raw data to do other analysis if needed later. This keeps the table size manageable.
I am tiring to replace a section of my code where I load "import" the text file into a table tblRawData. I have to delete all the records and import them for each record. I am thinking that just changing the link will be faster

One other note. I use the txt file as read only so I do not need to be able to edit anything in the file simply read and qry on it.
Thanks
Zack
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9884376
Hi,

Well, to be true -> I still don't understand why you're using text files ... please don't take this as critic or offense!

I did a lot with text files until Access 1.0 was released ... since then and up to Access 2.0 I had an alternative to text (flat) files (while mdbs were still flat!) -> later I worked with DAO (without using Access User Interface anymore) ... and since 1998/1999 I'm working with SQL Server ...

... what I see is that you're obviously in to working with DAO -> what is this text file thing about?!

Can you explain?


Best regards, Raisor
0
 
LVL 2

Author Comment

by:lightcross
ID: 9884484
our previous method of analyzing data was importing the text file and saving it in a temporary table.  then we ran sql's to analyze the table and take out our "critical" data only.  this method takes alot of time because we then have to delete the table and re-import the next text file.  we are now trying to switch to the dao method to avoid having to import text to table and delete over and over (the most time consuming portion of the process).

the text files are what is output from our testing equipment.  so we have no choice but to use the text files provided to us from our testing equipment.  the textfiles have way too many data points (16000+) so instead of storing all the data points per record we only take what we need for that record.  then we are moving on to the next text file to do the same thing.
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9885431
Hi,

First of all, there shouldn't be any overload driven by what I say or what I state here! ... ;-))

... but, why don't you work only with databases or filter data already in your testing equipment so that it is ambivalent to your needs?

About what programming language are we talking (Assembler?) and what system do you run your testing equipment on?

Is there any need to change business/development processes?


Best regards, Raisor
0
 
LVL 2

Author Comment

by:lightcross
ID: 9885533
the testing equipment is limited to what it can filter.  besides that saving all the "raw data points" is a must so we can look back via hyperlink and check for other things.  saving the raw data allows us to further add more queries and calculation at any given time.  

so do you know change the link of a linked table via vba?
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9885853
Hi,

I know ... but I'm still not in your text file thing ... which is my fault I must admit ... I'm -> by experience <- more the fitered importing-guy than the "linking" one!

Anyway ... maybe this gives you the information you need: http://www.mvps.org/access/tables/tbl0007.htm


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9885859
fitered=filtered
0
 
LVL 3

Expert Comment

by:jvf
ID: 10295463
Hello lightcross,
Here in our lab, I gather data from many different test instruments on a daily basis and store it to a db. Like yourself, I am forced in many cases to accept all raw data from an instrument and parse out what I need from many readings. Are you still in need of some help? If so, I will study your problem.
0
 
LVL 2

Author Comment

by:lightcross
ID: 10295574
yes we would still like to explore this situation.  it sounds like we have an identical situation.  i dont know why that other guy cared so much about saving our raw test data!  we save all raw data in special directories that are backed up and the hyperlink to those files are also stored in a field within the database.  the database only parses and saves certain data but by saving the raw data our engineers are able to go back and import it into more complex statistical analysers.

basically the importing of raw data to a temporary table takes very long.  i would like to use a linked table (text type) instead of actually importing all the raw data points into the temporary table.  and then when it loops to the next raw data file i can change the hyperlinked text file path (via code).

method now used:
import text file to temp table (very time consuming, 65k records and 325 columns)
analyze data
place analyzed data into permanent table

desired method:
hyperlink table (text type will not need to be imported just analyzed)
analyze data
place analyzed data into permanent table
0
 
LVL 3

Expert Comment

by:jvf
ID: 10295582
OK, I'll read through the posts and be in touch next week.
0
 
LVL 3

Expert Comment

by:jvf
ID: 10317254
Hello lightcross,

Interesting scenario you have there. Let’s test my understanding of the process so far (please be patient, I could be brain dead if I only had a brain). Per each record, your permanent data table contains the relevant data and a hyperlink to the raw data text file, yes? So, how does the process start? It seems records get added to the permanent table with raw data hyperlink entries first. At some point, the table is inspected (using some criteria of hyperlinks with no additional data? Or, as you mentioned all of them re-analyzed? The hyperlink files of these records are parsed to fill out the pertinent data. However, for some reason, the link loop doesn’t recognize the new hyperlink and things get ugly.
0
 
LVL 2

Author Comment

by:lightcross
ID: 10317374
currently:
users select a raw data file on his hard drive.  within vb we import the file to a temp table and also copy the raw file to a permanent directory on the network.  this link is saved in the record.  we then do our analysis on the temp table and also add analyzed data to the record.  then we delete the temp table.  the temp table is created using the usersname and other info so multiple persons can use the form at the same time.

now, if we can change it to not actually import the text file but rather link to the raw text file as a table the process could be more quick.  for instance if you go to the main access form with all your objects then choose the table listing.  right click it and you will you can link a text file as a table.  it seems to perform that operation quickly verses reading the file in and creating a table within the db.  if i am able to do this same procedure in vb i think it would help.
0
 
LVL 3

Expert Comment

by:jvf
ID: 10318070
OK, more questions later but I'm ready to try something. Don't know if I'll have time to try it tomorrow and I'm out Wed. but, as our gov. put it, I'll be back.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 3

Expert Comment

by:jvf
ID: 10328291
Hello lightcross,
      I have been testing the link procedure and can successfully link a text file through code and variable names. By the way, I haven’t tried your code. Are you saying it does or doesn’t work? Anyway, before I publish any code, I need clarification of some terms/words that you are using. You mention vb. Are you working within Visual Basic or within Access itself using VBA (Visual Basic for Applications)?  You mention TextTransfer (with Capitals). Are you referring to the TransferText method in VBA? This method can be successfully used for the link process. In VBA it is directly available but a reference to the Access Object Library must be made to use it in VB. So far I have been assuming you are using Access/VBA but the syntax might change slightly if done in VB.
      In general, I haven’t done a lot of “linking” so my understanding of the terminology might be incomplete. I mention this in reference your concept of changing the link of a table from one text file to the next. My understanding is that the process of “relinking” or “refreshing” a link is one of reestablishing the original link if it gets lost or broken. The link itself is just a “name” and a pointer to data.  When it’s time to link to a new text file, I believe the process will be to delete the existing link and recreate a link of the same name, but with the new path.
Also, although you might provide a true hyperlink in a record to easily view the raw data, a “hyperlink” isn’t necessary to create the linked table. Gotta go, I’ll be back Thurs.
0
 
LVL 3

Expert Comment

by:jvf
ID: 10329494
P.S.  I omitted another reason to reestablish a link: If the path changes, of course the link is broken. In this case, you can fix the link by supplying the new path to the file. With a database containing linked tables to a db on a server, this can easily happen as the MIA dept. fiddles around. I have written code with a dialog box enabling the user to point to a new location if the links are lost. This reinforces my feeling that links are links to file names. The path can be modified but not the filename so, as mentioned, you just delete the link and start over.
I think see now why your code example used DAO. I have seen it mentioned that a link created by using the Connect property is preferred over the TransferText method that I used. Why is it preferred? I haven’t a clue. It might be more efficient or at least less prone to crashing because it was designed for accessing remote data. Being an “old timer” at db code, I haven’t used DAO much because it’s “new”.  There is even another data access method called ADO. Besides, I’m a hacker. It’s my job to run the test lab, not to write clean, concise code. Whatever works, I say. Out of curiosity, I’m going to try both methods.
0
 
LVL 2

Author Comment

by:lightcross
ID: 10334796
sounds like your on the right track.  yes, we are using vba.  thanks for your help man!
0
 
LVL 2

Author Comment

by:lightcross
ID: 10334942
I think you are on the right track. We are currently importing the text files into a temp table then deleting all the records and doing it over again. I am hoping we can have a linked table, which we can just change the path to so we do not have to delete and import for each file.

The code I posted will not change the path and an error pops up.
Zack
0
 
LVL 3

Expert Comment

by:jvf
ID: 10345779
Ouch, I’m having a little computer trouble today. My main machine had lost the import text option in Access so I’m working on another machine for our project. I restored a backup image (you do have lots of backups and disk images don’t you?) but now I can’t get back on the network until somebody shows up (that’s why I call them the mia dept-where are they when you need them?).

You’re using VBA that’s good. We’ll keep it simple and use the TransferText method for the first try. I have used the Connection property but, being a novice at it, the syntax gets a little hairy for me sometimes. Especially if trying to run a query. Actually, the TransferText method may hold an advantage for you. The DAO Connection Object is “non-persistent. That is, it’s only available while the code is running. If you don’t “Close” it, it will get disconnected anyway after a while. This means a Connection Object doesn’t provide a link that you can see in the table window, whereas the VBA code will establish a link that can be used whenever you are ready (like if your procedure is sort of manual and you run a query “later” in the process). I’ll work out some code and post it ASAP (but maybe tomorrow).
0
 
LVL 2

Author Comment

by:lightcross
ID: 10346195
We currently Use DAO for analyzing the data and a temporary link only when the code running is ok.
FYI
Zack
0
 
LVL 3

Expert Comment

by:jvf
ID: 10348416
Hello lightcross,
      I keep reading things that lead me to believe DAO can be used to connect to text files but I haven’t been able to do it yet. So, to get started, here is a “simple” method (actually, we do use DAO to make the tables, recordsets, etc.).  BUT-I had to make two assumptions so post ASAP if I’m wrong. 1) the first line of your text file contains headers (field names) and 2) it is delimited (as opposed to fixed length). All instrumentation I’ve used (or programmed for) so far has used headers in the text but some files have been delimited and some are fixed. I placed the following comma delimited files in a test directory—“C:\test\”

File1.txt:

aaa,bbb,ccc,ddd,eee
1,2,3,4,5
11,22,33,44,55

File2.txt:

fff,ggg,hhh,iii,jjj
1,2,3,4,5
66,77,88,98,1234

I also made a table named hyper with a hyperlink field named “link” and added one entry pointing to C:\test\file2.txt



My code will link the files and query record#1 for the value in the first field, deleting links as necessary. Deleting the link is like deleting a table. I delete temporary tables all the time and have written a function for this so code won’t crash if someone tries to delete a non existent table. You may decide to just delete directly.

We have to get the tablenames and paths somewhere, but I think you have that in hand. I will hardcode the tablename. The first link path will be hardcoded and we will get the path for the second link from the hyperlink table

We will use two buttons “cmdLink1” and “cmdLink2”

“cmdLink1” will delete the link to file2 (if it exists) and create a link to file1 .You will see the link in the tables

“cmdLink2” will delete the link to file1 (if it exists) and create link to file2 .Again, you will see the link in the table window

This may not even be close to what you need but it provides a starting reference. Speaking of references, since you are already using querys, etc. I assume you have correctly referenced a DAO library.
‘code---------------------------------------

Public Function DeleteTable(MyName$)
Dim db As Database
Set db = CurrentDb

Dim tdf As TableDef
   
    For Each tdf In db.TableDefs
        If tdf.Name = MyName$ Then
        DoCmd.DeleteObject acTable, MyName$
        Exit For
        End If
    Next tdf
Set tdf = Nothing
Set db = Nothing
End Function


Private Sub cmdLink1_Click()

Dim db As Database, rs As Recordset, a$


Dim MyTable$, Myfile$
MyTable = "Barfy"
Myfile = "C:\test\File1.txt"
'delete the table if it exists

DeleteTable (MyTable)
‘create a link
DoCmd.TransferText acLinkDelim, , MyTable, Myfile, True

'barfy lives!, lets look at the first record
Set db = CurrentDb
a$ = "Select * FROM barfy"
Set rs = db.OpenRecordset(a$)
rs.MoveFirst
'we could call out the field name but lets use it's position
' 0 based
MsgBox rs.Fields(0)

'clean up
rs.Close
Set db = Nothing






End Sub


Private Sub cmdLink2_Click()

Dim db As Database, rs As Recordset, a$


Dim MyTable$, Myfile$
MyTable = "Barfy"
'delete the table if it exists

DeleteTable (MyTable)


'let's get the path from the hyperlink using the hyperlinkpart function
Set db = CurrentDb
a$ = "select * FROM hyper"
Set rs = db.OpenRecordset(a$)
rs.MoveFirst
Myfile = HyperlinkPart(rs!link, acAddress)


‘create a link
DoCmd.TransferText acLinkDelim, , MyTable, Myfile, True

'barfy is re-animated!, lets look at the first record

a$ = "Select * FROM barfy"
Set rs = db.OpenRecordset(a$)
rs.MoveFirst
'we could call out the field name but lets use it's position
' 0 based
MsgBox rs.Fields(0)

'clean up
rs.Close
Set db = Nothing

End Sub

‘--------end code

0
 
LVL 2

Author Comment

by:lightcross
ID: 10348517
yes there are headers and yes it is tab delimited.  it looks like you are deleting the table rather then changing the link of the table.  do you think it would process faster if you just changed the link of the linked table?  i cant see how it would be faster but just curious.  we will look into your method asap.  let me know if you would like to see an actual text file we are messing with.  i can email it to you.  drop us a line at "experts at lightcross.com"
0
 
LVL 3

Expert Comment

by:jvf
ID: 10348993
Well, that's the trick. The table created by the code is just a link, not a real table. If you see it in the table window, you will notice it's a funny icon (notepad in my case) with a little arrow next to it. But, the link is "erased" by using a "delete the table" syntax. We're just erasing the path and starting over. If your assumption is correct that linking will be faster than actually importing the data to a temporary table then you should see a speed increase. I cannot (yet) sucessfully change the file name of a link path. Therefore, I think it doesn't work because a link can get it's path changed but not it's file name. I think I did see a reference to changing the path AND file name for a DAO Connection Object but I can't even connect to a text file yet, let alone change the properties. DAO was designed to connect to other databases and I'm still not sure it can actually connect to a text file. But, as I said, I'm a novice at using the DAO Connection Object. I would like to see a file. I'll email you.
0
 
LVL 3

Expert Comment

by:jvf
ID: 10349015
P.S. Maybe it's confusing because of using TransferText. Although you were using it to actually TRANSFER text, I use one of it's options (acLinkDelim)  that enables it to create a link instead of actually transferring any data.
0
 
LVL 2

Author Comment

by:lightcross
ID: 10349094
We will try it out on or end by next week Have some more urgent items right now. Your help is appreciated.
Zack
0
 
LVL 3

Expert Comment

by:jvf
ID: 10354000
Good luck,
      In the meantime, I’ve been reviewing the DAO articles I’ve collected over the last couple of years. Although geared for accessing remote databases, there are definitely drivers for connecting to text files. I found some connection syntax and will try it soon. Upon success, I’ll post it so you can compare methods.
0
 
LVL 3

Expert Comment

by:jvf
ID: 10385296
Hi Zack,
      Man, that file you sent is as ugly as some of the files I have to parse for our Biology dept. I got your post that is actually a raw file. As I mentioned, my code wouldn’t link to it at first but I believe I’ve worked that out.
      I have a plan of attack to try and speed your process up but first we need a brief review of data access methods. Because I don’t fully understand the subject, some of this explanation will be a little tweaked but I believe it’s close enough. Two common scenarios are 1) wanting to access data via code in order to get some information and, 2) wanting to link or import data to another database. The data to be accessed could be a file, spreadsheet, etc. or in another database. In order for applications to accomplish this task, they make use of ISAM drivers. Here is a definition from the web: ”… Indexed Sequential Access Method, a method for managing how a computer accesses records and files stored on a hard disk…” Also, in VBA, we are using the “jet database engine”. Using DAO in the jet database scenario we can work with the Connection PROPRTY of a table (I believe your posted code is such). Because of the apparent refresh problem, I was concentrating on the Connection OBJECT that bypasses the jet database engine and doesn’t need to make a table (or table link) to connect to the data (better in some cases-irrelevant for us). The TransferText method doesn’t use a “connection string” per se, but I believe it must also use an ISAM driver in some capacity.
      Whether using the Connection Property or Connection Object, a connection string of proper syntax must be provided. Within the string, reference is made to the (hopefully) appropriate ISAM driver. It turns out that, like TransferText, use of the connection PROPERTY also results in a linked table visible in the database window. The Connection OBJECT is the non-persistent view of data and will be lost when the code is done. All this stuff is geared towards expecting the data to be in the “standard” field/record database format or, in spreadsheet language, rows and columns. When the file is squirrelly, like your instrumentation data file, the application starts complaining.
      For instance, for some reason, the link code (either TransferText or a Connection string) failed to import your file. But, the Import text wizard could successfully import the file if I specified no field names in first row and tab delimited. These difficulties have been anticipated somewhat and to help the code work, there are some ways around this. One is to make a special file named Schema.ini that contains “instructions” on how to deal with the file contents. The Schema file is placed in the directory containing the text file and the Connection property code will use this file when opening it. A disadvantage of this method is that the Schema file must contain the name of the text file. This sounds like a problem for you. If many files must be opened, they either all have to be in a different directory or you would have to constantly change the Schema file via code with multiple users battling it out. Another way is to save the Import Specification derived when using the Import Wizard to import a text file. It’s given name is saved in a hidden system table. This method can be used with TransferText. It has a great advantage in that it is “generic”. That is, it doesn’t care what the file name is. In fact, you could save different specification flavors if some files needed a different approach. Even when it works, using the Connection Object can get complicated because the proper drivers must be installed. This can get problematic and just adds another layer of complexity. Also, as stated, although there are drivers (and syntax) for text files and spreadsheets, most connection strings assume connection to another database of some flavor. I have found (and will post) connection string syntax that will connect to a text file when using the Connection PROPERTY. All indications are that it should work for the Connection OBJECT also, but I can’t get it to happen yet. Also, code examples indicate that contrary to what I thought, the refreshlink method can be used to refresh a link to a table even if the file name changes. Again, I haven’t been able to accomplish this yet. However, all these examples are using a connection string that connects to a database, not a text file.
      To sum up, so much for Academia, let’s get real. I believe it’s fair to say that you aren’t so much interested in connection strings or link refreshing as you are in speeding up your data collection process. In this respect, we can only hope that your hunch is correct in that linking, rather than importing, will speed things up. However, if you have to transfer very much of the file to parse it properly, you may lose the linking advantage. To prove that links created by my code didn’t increase database size; I checked its size after each method (compacting along the way as necessary). As expected, the database size was not changed by the link but increased by around 4M (your approximate file size) when the file was imported. I used both the Connection/ Schema.ini method and the TransferText/Import Specification method (recommended). Note that my simple, straightforward file examples can be linked by either method without help. So, my plan would be to use the TransferText method with appropriate Import specs as necessary along with deleting and recreating the links as we go. If this seems like a plan that you’d like to try we can go over some details and test it out.
0
 
LVL 3

Accepted Solution

by:
jvf earned 500 total points
ID: 10394069
OK, in case you’re curious, here is some code using the Connect Property. Paste it behind a button and try it. The gist of my last post was, although I am frustrated that I can’t get the Connection Object to work, all of the methods that do work will eventually use the same ISAM driver and so will have the same speed. Further, I’m sure there won’t be any measurable speed difference between deleting and recreating a link or refreshing it. What really troubles me is that VB/VBA code is not efficient with string manipulation. This has been discussed in many articles. One idea of speeding things up (that appealed to me as a part time code warrior) was to write a dll (in C) to do the parsing and pass text to it. Maybe someday…
This makes me wonder if your lack of speed is due not to the importation process per se, but to the parsing that comes later. We’ll know once you try it with a linked table instead of an imported one.
      
‘---------------------connection code

DeleteTable ("Barfy")

Dim db As Database, rs As Recordset, a$
Dim tbl As TableDef

Set db = CurrentDb
Set tbl = db.CreateTableDef("barfy")
   
tbl.Connect = "Text;database=c:\test"


tbl.SourceTableName = "file1.txt"



db.TableDefs.Append tbl

a$ = "Select * FROM barfy"
Set rs = db.OpenRecordset(a$)
rs.MoveFirst
'we could call out the field name but lets use it's position
' 0 based
MsgBox rs.Fields(0)




Set tbl = Nothing
Set db = Nothing

‘-------------------end code


0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now