Append query - change source table at runtime

[TheToeOfPooh]

I would appreciate any sugestions as to how I can make the following process as simple as possible for the user.

Currently to update a master table on a daily basis they have to:-
TABLES
Link Table
Link a new Excel sheet each day (e.g. Augprn31)
QUERIES
Append new source table to a master table called tblCurrentMonth
By using
In "Design View"
Show table (the new table is selected e.g. Augprn31)
Table criteria using drop down option to replace the existing source table with the new one. e.g. Augprn31 will replace Augprn30

THE PROBLEM
This has to be done for each of the 21 fields which are appended to tblCurrentMonth.

I have tried using the right-click method to display the query properties which could be used to update the Source database (current). This scared the bejazus out of the user.

If possible a list box containing the table names would pop-up when the design view is opened for this append query?

Any sugestions please as I can build a pretty basic database incl tables, forms, queries and reports but have no experience of "customising".

Bernard

 

[SydneyGeek]

Just a thought -- this can be done in Access but maybe the Excel side of the process is the place for the creative thinking.

How's about:
1. Create a linked table like you have. This can be called whatever you like. Set up the queries and reports so they work OK.
2. Each day, copy the new data into this linked spreadsheet. The data can either completely replace what is there, or be appended to it -- Access will see it either way.
This has some advantages:
a. Relatively esay to script on the Excel side
b. No fiddling with the logic in Access, once you have it working as you want
And some disadvantages:
a. Once you have lots of records (several thousand), linked tables get pretty slow.
b. If you REALLY have heaps of records, 65536 rows may not be enough.
Another option:
3. Create a file that gets used as the import file -- like the linked example above. Then import it as you have been doing before, and append the data.
Advantages --
a. Again, no need to change any of the logic in Access
b. You import one day's records at a time, replacing the previous set in the import file.

Caveat for all of the above -- you MUST use the same layout and field names every time. Also, Access imports much better if you make a named range in Excel. That way you don't get the Field1, Field2... rubbish

Post back if you need clarification on any of this

Denis

 

[TheToeOfPooh]

Option3

I am always open to some lateral thinking.

Denis - can you please elaborate a bit?
Currently
Source of data = dB2
File created e.g. Augprn31.csv
Then saved as Augprn31.xls

This is the step I am unsure of...
Average number of records per file = 3,700
Same field name and layout every time

If I used a file which acted as the Link
e.g. (Please correct me if I've got this wrong)
Yesterday.xls
Into which augprn31 data goes (to replace augprn30).
using this method at some point would'nt Yesterday.xls be empty?
Would this then cause lots of errors in the associated Append Queries??

If this is not the case - can you help get me started with relevant VBA code on the Excel side as I suspect I will need some kind of List Box which the user will use for the relative refference to
1) Add new data from Augrpn31
2) Delete data from Augprn30

Thank you for your time so far.
I appreciate the help.

Bernard

 

[SydneyGeek]

Option3

{snip}

This is the step I am unsure of...
Average number of records per file = 3,700
Same field name and layout every time

If I used a file which acted as the Link
e.g. (Please correct me if I've got this wrong)
Yesterday.xls
Into which augprn31 data goes (to replace augprn30).
using this method at some point would'nt Yesterday.xls be empty?
Would this then cause lots of errors in the associated Append Queries??

{snip}

Bernard

Hi Bernard, replacing one set of Excel records in Yesterday.xls with a new set won't upset Access unless you fire off the queries while the file is empty. So, if you first do the Yesterday.xls update, then run the Access queries, you should be fine.

Denis

 

[SydneyGeek]

Bernard,
this code's a bit clunky but it does the Excel side of things for you.

Sub TransferTodaysRecords()
  Dim NewData As String
  Dim CurrPath As String
  Dim Rw As Long
  CurrPath = ActiveWorkbook.Path & Application.PathSeparator
  
  Workbooks.Open CurrPath & "Yesterday.xls"
  Rw = Range("A65536").End(xlUp).Row
  Range("A2:U" & Rw).Clear
  
  NewData = Application.GetOpenFilename
  Workbooks.Open NewData
  Rw = Range("A65536").End(xlUp).Row
  Range("A2:U" & Rw).Copy
  Windows("Yesterday.xls").Activate
  Sheets("Sheet1").Range("A2").PasteSpecial
End Sub

Edit: I put this code in another fiel called Processor.xls It lives in the same folder as Yesterday.xls and you run it first, before doing the Access update.

Denis

 

[mdmilner]

I'm curious, how do you get the data out of the db2 table into excel?
FTP?

Why not skip excel and import it directly into the Access database?
If you save it as the same tablename each time, you can reuse your append query.

Mike

 

[bat17]

Rather than linking and appending have you looked at using the import wizard which will let you append straight to your master table?

Peter

 

[TheToeOfPooh]

Denis
Thanks for your help so far. I will post status once I've used your code.

Mike
Yes - I telnet into db2 then FTP file across. (Bet this news generates a sugestion 8 -) .

Peter
I will follow through with sugestion from Denis then investigate use of the Import Wizard. Thank you for the tip - I'll post back results.

TTFN - off to try the options.

Bernard

 

[TheToeOfPooh]

Denis

The following works as you predicted.
Sub TransferTodaysRecordsMacro2()
'
' TransferTodaysRecordsMacro2 Macro
' Macro recorded 01/09/2004 by Bernard Elgar
' As result of help from Denis at MREXCEL Board
' Keyboard Shortcut: Ctrl+Shift+Z
'
Dim NewData As String
Dim CurrPath As String
Dim Rw As Long
'In the current directory
CurrPath = ActiveWorkbook.Path & Application.PathSeparator
'Open Excel Workbook called Yesterday.xls
Workbooks.Open CurrPath & "Yesterday.xls"
'Delete all data from A3 to U
Rw = Range("A65536").End(xlUp).Row
Range("A3:U" & Rw).Clear

'Select the .csv file required.
NewData = Application.GetOpenFilename
'Open the selected Source File
Workbooks.Open NewData
'Copy all data from A1 to U
Rw = Range("A65536").End(xlUp).Row
Range("A1:U" & Rw).Copy
'Open Destination Workbook and sheet
Windows("Yesterday.xls").Activate
'Paste Special FROM A1:U in Source TO A3:U in Destination
Sheets("print 3 Copies").Range("A3").PasteSpecial

End Sub

I have therefore been able to reassure the user while still achiveing the reporting updates they required.

Gaw'd bless you sir ray:

Bernard

 

[mdmilner]

Denis
Mike
Yes - I telnet into db2 then FTP file across. (Bet this news generates a sugestion 8 -) .

Well, yes it does. I've recently begun a slow process (in between other tasks) of building a mdb to automate a repetitive task - weekly/monthy xls report creation. Basically I built a routine that allows me to 'script' a series of actions, including FTP. Currently, I'm using a Shell command to SendKeys the FTP commands - but it's going to be removed when I have a chance to rewrite it using the Microsoft Internet Control library (I've got it, tested it, just haven't re-written to use it yet)

I wouldn't mind sharing, but I'm just flat out swamped and don't have the time to fiddle with it this week. It's actually another component of this project that I already posted here:

http://216.92.17.166/board2/viewtop...mexspecs&sid=425987e9684c6bd977332d84bc5fb6ea


Mike