Oracle Database to Access

padlocked17

Registered User.
Local time
Today, 15:57
Joined
Aug 29, 2007
Messages
276
All,

I'm working with being able to access data from an Oracle Database. I can't link to the Oracle Database through an OBDC connection due to computer limitations so I have one user that uses Access to create a Batch file that downloads text files containing all of the data I need everyday. I then have the text files linked in Access with Append Query's that move the data into the Database.

The issue I'm running into is that I want to be able to change the location of the linked tables. Every tool and sample of code I have come across can change the linked tables, but when it comes to the linked text files, I can't seem to figure out how to relink them. Should I stick with this approach of having linked text files, or should I take some other approach.

The advantage I have of using linked text files is that I can use the batch files to update the text files on a daily basis, run my queries, and have up to date data in the database.

Any suggestions or ideas?

Thanks!
 
You can update linked (text) tables using VBA. I have "template" databases that use linked tables and must be copied off network drives to the local computer so before anything runs it updates the linked tables to make sure that they're pointing in the right place. The linked tables are created before hand as necessary (fixed width, delimited etc) so it is only updating the filepath rather than attempting to modify columns etc.

You could be a bit smarter than just looping through all the tables looking for linked data sources but sometimes a blunderbuss approach covers the bases.

Code:
Set db = CurrentDb
  
'[b]This is the bit that points to the Folder where the text files are located[/b]
  strSourcefilePath = CurrentProject.Path & "\LinkedTables"
  
  For Each tdf In db.TableDefs
    'only linked tables have a tdf.connect that contains a string
    If tdf.Connect <> "" And Not tdf.Name Like "~*" Then
   
      'find where the database part of the connect string is located
      intposition = InStr(tdf.Connect, ";database=")
      
      'trim the connect string to that position
      strConnect = Left(tdf.Connect, intposition)
      
      'reset the file path
      tdf.Connect = strConnect & "DATABASE=" & strSourcefilePath
      
      'refresh the link
       tdf.RefreshLink

    End If
  Next

If you look at the tdf.connect element of a linked text file you should see something along the lines of:

Code:
Text;DSN=textfile Link Specification4;FMT=Fixed;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=C:\folderpath

All the code is doing is updating the folder path (not including the filename) and then refreshing the link.

There is a slightly different routine for updating links to non-Text sources (excel basically) because it would be far too easy to treat all external linked tables in the same way :D
 
or should I take some other approach.

The advantage I have of using linked text files is that I can use the batch files to update the text files on a daily basis, run my queries, and have up to date data in the database.

Any suggestions or ideas?

I would think that scripted import of the text files into an Access DB would yield better performance than using text files as a database.

For daily import, I would suggest a scripted process that copies a clean / empty Access database to a defined target location on disk EACH DAY and script run the import.

I have such a process in place which downloads records from an AS/400 and publishes the new data to SQL Server. The master DB is totally empty / compacted / compiled. The scripting makes a copy of that master DB to a run-time location, launches it, and the DB itself has a form with a count down timer (which can be canceled) and once the timer count down completes it goes to work downloading from the AS/400 into Access tables, then publishing the Access tables to SQL Server.

So Import those text files into a clean / empty Access DB daily is what I propose. Access DB's surly trump a text file being used as a database, Ja? :rolleyes:
 
You can update linked (text) tables using VBA.

Thanks for the code. I will take a look at this and see about utilizing it.

mdlueck said:
For daily import, I would suggest a scripted process that copies a clean / empty Access database to a defined target location on disk EACH DAY and script run the import.

Do you have any more examples/code that I might be able to utilize to accomplish this? I love the idea, now I just need a pointer or two to make it a reality.

Thanks again.
 
Do you have any more examples/code

To download into an Access temp table:
Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

To publish to a SQL BE DB:
Using VBA ADO objects to execute a Stored Procedure
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

Should you encounter NULL values, those need special handling:
Ability to store NULL via VBA ADO Stored Procedure
http://www.access-programmers.co.uk/forums/showthread.php?t=219562&page=2#post1120572
 

Users who are viewing this thread

Back
Top Bottom