Linked Table

[klyon]

My question is a two-parter, so here's some context first.

I'm trying to automate the process of deleting a linked table, open a network directory and create a new linked table using a .csv file that I select.

So, first, can I set up a table definition for a linked table and, second, how do I automate the open directory/select file/create link (hopefully with table def)?

I'm using Access 2000 on Win2000Prof.

Thanks in advance for your help!

-Kris

 

[LonnieJohnson]

Are you needing to open the directory for any specific reason? Like file name? I would think you could use the TransferDatabase method and use an InputBox to get the file name. You should also be able to use the DeleteObject method in VB to delete the first link. Is it in the same folder all the time? What changes about the file that you have to severe the link and re-establish it?

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[klyon]

Thanks for the quick response Lonnie.

The file name changes each day 'same text'&'yyyymmdd'.csv, but the linked table name needs to remain static in order for the rest of my processes to run. It is in the same folder each day.

I understand the deleteobject action, but am not sure about the TransferDatabase with the InputBox, could you elaborate?

Thanks again!

-Kris

 

[LonnieJohnson]

I'm sorry, it's actually the TransferText method. Here is an example I thru together using an InputBox.

Dim strFileName As String
strFileName = InputBox("What if the filename?&quot

If IsNull(strFileName) Then
MsgBox "There was no name entered."
Exit Sub
End If


DoCmd.TransferText acLinkDelim, , strFileName, "C:\" & strFileName & ".csv"

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[klyon]

Thanks again Lonnie! The link operation worked perfectly!

Is there a way to keep the linked table name the same (from day to day - not the same as the text necessary for the new file). It is naming the linked table with the text that I enter. A couple of my labels bombed as well since I haven't been able to create a table definition - any ideas on that?

Once again, I'm very grateful for your help!

Best,

-Kris

 

[LonnieJohnson]

USe the modified code below:

Dim strFileName As String
strFileName = InputBox("What if the filename?&quot

If IsNull(strFileName) Then
MsgBox "There was no name entered."
Exit Sub
End If


DoCmd.TransferText acLinkDelim, , "TableNameHere", "C:\" & strFileName & ".csv"
prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[JoeMiller]

Another avenue to explore is modifying the TableDef's Connect property through VBA every day to link to your new .CSV rather than deleting the db and linking a new one. Look up tabledef object when in a vba window and click Properties on the resulting help page for more info.

While Lonnie's method certainly works, it can cause fragmentation in your DB over time as Access doesn't purge deleted data (ie: your tables) until you perform a DB Compact.

HTH Joe Miller
joe.miller@flotech.net

 

[klyon]

Wow - I just joined this forum today and I am extremely pleased!

Thanks again Lonnie - that fix hit the nail on the head.

Joe - thanks for the idea - I'll look it up. First thing I do when I start a new DB is turn on the Compact on Close
My staff (who are closer to real programmers that I am) got tired of my enormous db's

Best to both of you!

-Kris

 

[mraj]

Hello,

I want to transfer my data from an access table to a dbase IV format. I mean the export procedure. I wan to do it programmatically.

I tried using TransferDatabase but to shouts saying Dbase IV not installed, or cannot use this operation for this purpose. But i tried doing it manually, by export from file menu, and it does.

Can anybody please help me on this.