Import Automation

[jsparks]

I have an Access 2000 database on an NT4 server, and I have data that I need on a UNIX server. I have the UNIX server setup to create 3 .CSV flat files automatically every day. The NT4 server copies those files from the UNIX server, every day.

Currently I have those 3 .CSV files linked as tables in the Access database, and for the most part it works great. However, now that I am on to the reporting phase with Crystal Reports, I'm running into some serious limitations and problems using this method.

I need to have the data that's in these 3 flat files as real Access tables in the database (not linked tables.) I'm hoping to get some suggestions (or an application) that will automatically import the data from the .CSV files every day at a specific time. Another possibility is to have some sort of routine that deletes the tables from the database and then creates 3 new tables based on the .CSV files.

I'm stuck on this and need to get around it to do some important calculations in my reports. I am not all that familiar with VB/VBA but have the training materials here to dig if that's the road i need to be on. I could really use some help to get me on the fast track in the right direction, though. Any would be appreciated. Thanks in advance!

Jim Sparks

 

[Rohdem]

Look at the Transfertext VBA command. That will get you on the right path. Mike Rohde
rohdem@marshallengines.com

 

[jsparks]

I'm reading in Wrox "Beginning Access 2000 VBA" about the Transferdatabase and Transfertext commands. However, both of these examples suggest you put a button on a form in the database and use the onclick event to run the VBA code with the Transfertext command in it. That looks like it will work, but I would need to adapt this to something I can run externally - like a vbscript or something...something I can tell the scheduler to run every day. Any suggestions? I may use the book's suggestion and just make a form in the database and manually click on it every day.

 

[Rohdem]

You could also create a form that is a 'timer form'. Forms have a 'on timer' event that runs at the interval specified using the 'timer interval' property. For example, if you set the timer interval property of a form to 4000 (it is in milliseconds) and placed the VB code into the 'on timer' event:

Private Sub Form_Timer()
msgbox("HI&quot
End Sub

Then, whenever the form was open, you would get a message box that says HI every 4 seconds.

Now if you used something like this with a timer interval of 1000 (1 second)

Private Sub Form_Timer()
if TimeValue(Now()) = 1:00 PM THEN
docmd.transfertext.......
end if
End Sub

Then your transfertext command will run when the system time hits 1:00 pm.

Mike Rohde
rohdem@marshallengines.com

 

[MichaelRed]

MikeRhodes.

O.K. but ...

It will consume a LOT of time ding the timer thinggy.

Further, It may NEVER be exactly equat to 1:00 P.M.

I, personally, would probably set up a procedure to generate the report. In that procedure, I would do the manipulation of the data to have the recordsource for the report 'set up' and then just open the report.

Jim Sparks,

You say that you "NEED" to have the CSV files as native access databases, buyt give no reason for this "need". I have use Ms. Access for over 10 years (Since ver 1.0!). I do not recall ever needing to "import" a file which was 'acceptable" as a linked file to use w/ Ms. Access. [red]ALL[/red] data transforms and manipulations should be available from the "linked" CSV file. The Import process MAY speed up your process a VERY small amount - at first, but will eventually slow your process down and probably force you to compact the database more frequently. If you have evidence to the contrary, PLEASE share it w/ me, as I am always still learning.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jsparks]

Michael :

I am using Crystal Reports 8 to develop reports based on the tables in this Access database.

I'm not sure if you are familiar with CR8, but I run into 2 different problems there. First, if I try to specify my database as the datasource for the report by choosing the system DSN from the ODBC tree, the linked tables are not listed as tables that can be added to the report. Second, if I use the "Find Database" tree and browse and choose the actual .MDB file, the linked tables ARE listed, but when I try to add them as sources of data to the report I am asked for a password. I have absolutely no security setup on this .MDB, and the file permissions for the .MDB and the entire directory (and the .CSV files) is completely open. I've tried the admin password for the machine and it doesn't work.

While trying to resolve that issue in CR forums and with CR tech support, I am also trying to find a better way to bring the data into Access. CR has no problem opening any of the other tables in the same database, only the linked tables. That's why I say I "need" to have the data as native access tables.

Thoughts?

 

[MichaelRed]

Jim,

I do not use Crystal reports. I never saw sufficient 'advantage' in CR to warant the additional expense. IMHO, even the additional 'hassle' (such as you mention) are reason to avoid third party "add ins". the only useful capability in CR (again IMHO!) is the capability to 'rotate' labels.

My suggestion would be to 'regress' to plain old Ms. Access Reports and 'get over it'. Doutless, many (probably including yourself) will heartily disagree, however my opinion is just my opinion, and it will remain my opinion. I have had assignments which required the use of CR, and I have always complied with a customers requirements where possible, If this included CR, I also produced the reports in Ms. Access (where Ms. Access was the Application development environment. In numerous cases, when my customers saw the reports in Ms. Access -FOR FREE- they removed the requirement (and use) of CR.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Rohdem]

Michael,

If you set the timer interval to one second, it will fire at the specified time. I have several databases that do updates overnight this way and I have yet to have one not fire at the specified time.

You are right, the timer will slow the database down considerably if you are trying to use it for data entry, etc. What I usually do is open the form before I leave everyday and let it do its work overnight. It all depends on your situation. I was merely suggesting a solution to what Jim wanted to do....update the tables at a specified time.


Jim,
As far as Access vs. CR goes, having not used Crystal reports I really can't comment on which is better, but I will say that MS Access reports are very easy to produce and I have yet to come accross a report that I couldn't generate using Access. Mike Rohde
rohdem@marshallengines.com

 

[Katerine]

Another possibility to prevent the "OnTimer" lag (and this is just something I came up with off the top of my head.. you might want to take with a grain of salt) is to set the timer interval to .. oh .. 5 minutes (300000 ms), and have the following code (pseudocode not tested is included below):

--- Begin Code ---
dim bolAlreadyRun as Boolean ' Module-level

Private Sub Form_Timer()
dim dtBottomInterval as Date, dtTopInterval as Date

dtBottomInterval = TimeValue(13:00)
dtTopInterval = TimeValue(14:00)

if TimeValue(Now) < dtBottomInterval Then
bolAlreadyRun = False
elseif TimeValue(Now) > dtTopInterval Then
bolAlreadyRun = False
elseif Not(bolAlreadyRun) Then
docmd.TransferText........
bolAlreadyRun = True
end if

misc error code handling
End Sub
--- End Code ---

This will run the TransferText at some point (only once) between 1 PM and 2 PM each day.

 

[THoey]

I am not too familiar with W2K, but does it have a AT command like WinNT does? If so, how about this:

1. Create a second MDB that has links to your tables that you want updated and the TransferText setup.
2. Create an Autoexec macro in this new MDB to automatically call the TransferText for each of the three files/tables and then close the database.
3. Create an entry in the AT to have this new MDB called at 1 a.m.

This way, no timer, data loaded automatically each night. You just need to make sure that the data gets from the Unix box to a predetermined directory.

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

I just talked to a friend that is a bit more familiar with W2K. There is a TaskManager that you would have to look up, but he also mentions that the machine would have to remain logged in and unlocked.

Again, I am not sure, but worth looking into... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.