Transferred from Tables as suggested. (1 Viewer)

[Bigschnoz]

Firstly I must admit to being extremely weak in terms of Access ability.

I am looking to build a sales forcast tool (front end may yet be access or excel). The original data comes from 3 sources which are linked comprise the bulk of the data I need with one exception. I need to break down the cost element of each line entry (job number) in the final dataset "SalesForecast".

The query created from the 3 data sources currently comprises around 4000 records and this will increase by about 1500 per year. Each individual record in this expanding dataset however needs linking to cost data which is stored in a rather strange fashion.

Each "job" in the dataset has its own individual database which holds some data not available in the main dataset. It is stored in a \year\month structure:-

\\server\shared\etc\"year"\"Month"\"Job Number.Mdb
(e.g. \\server\shared\etc\2012\Apr\5678.mdb)

I have created a linked table to a spreadsheet where each cell has a path to the relevent database which links to each job number in the main dataset.

\\server\shared\etc\2008\Jun\1033.mdb
\\server\shared\etc\2008\Jun\1034.mdb
etc.

I need to import or link to a table called "CostItem" within each of the databases and sum the values for the field "ItemCost" where the values of the field "Description" = "Fitting" (There can be multiple entries in the field "ItemCost" and therefore this needs to be summed in order to generate a single value of "CostItem" per line in the main data.

My first problem is I do not have a method of coding or creating a query for the list of db file paths that would enable me to link to data from thousands of databases within a single query or table. Any tips/clues would be gratefully received .

Once this issue is resolved I should be able to sum the cost information I am after then link this to the main "Sales Forecast" table.

Many thanks in anticipation
Steve

 

[VilaRestal]

As I mentioned on the other thread, I think you will need to have a table that records the progress of the import:

tblImportProgress
ID autonumber
DatabasePath text - each database that an import is attempted from
ImportSucceeded yes/no

Obviously, the import will proceed in a loop. Each iteration, it puts an entry in that table at the start and then marks success or failure at the end.

I also think after every X iterations it should prompt the user to continue. The code should be able to look into that table and resume where it left off. The idea being: the user can back up the database at intervals and then resume the process. (Perhaps that backup could be automated.)

And also as I said on the other thread this code should be importing the data so that these thousands of databases can be got rid of. I don't recommend that you just get the stats you want and then leave the current system in place. Take this opportunity to get rid of that horrendous design if it all possible.

The final figure you're looking for would then be a trivial query of the data now all in one place.

I'll start thinking about the code later.

And I repeat, I think linking is not an option. And if you're doing this with a view to disposing of those databases then you must be importing.

 

[Bigschnoz]

Thanks for reply.
Unfortunately I have no control over the data structure as it is built into a proprietary application we use and I am sure they wont modify the "Noddy" structure any time soon.

I am only looking to extract a small amount of data from each of the individual databases therefore the processing overhead shouldnt be excessive and the resultant database size will be fairly minimal. It would make sense to create static data in a table rather than linking, I would need to create some mechanism to record the last record updated and enable the import to run from the last record. Looks like I need to dig out my old VBA books over the weekend.

2003 version by the way.

 

[brumster]

Have a look at the Scripting class made available as part of the Microsoft Scripting control (I'm on 2007 but I hope 2003 is similar, if not identical). There are various methods for navigating file system objects, folders, files and so forth - hopefully none of them constrained to local file systems only - google "VBA scripting filesystemobject" and that should set you off with some reading.

You will be able to get a context to the top level folder, gets it's contents (files, folders or both) and iterate over them, finding all the leaf files you need. If it's properly structured, as your post suggests, then you might be able to 'hard-code' the paths it looks for, but a recursive routine that just searches down from a top folder and finds all the ".mdb"'s would be resilient to change.

edit: I also echo the comments above; a system that utilises thousands of individual .mdb's shows some lack of foresight, and unfortunately you're the one paying the price for it now

 

[Bigschnoz]

Thanks Brumster, I will take a look at it

 

[VilaRestal]

Indeed you'll need the Microsoft Scripting Runtime reference to make use of the FileSystemObject at least for error predicting (checking files exist before trying to import from them).

Even though it will be simpler because you're just importing a small part of each database, I still think having some method of logging progress would be wise. If it crashes at a certain late point you'd be glad of it. Just having an extra field in the import destination table that records which database the record came from would be better than nothing. You could then select unique values from that field to have a list of imports that have been completed.

 

[Bigschnoz]

Re Pat's reply in tables thread, yes I agree wholeheartedly that the architecture of this applications database(s) is maddening and nonsensical. The individual databases per job hold some line level data which could simply be held in a single table and archived once in a while if size became an issue. The tool I am trying to develop however needs to access the job's "fitting cost" from the individual databases as this cost is wrapped up in an aggregate total job cost figure in the main database.

I have had a look at fso (initially in Word/Excel) and have carried out a couple of excercises to show drive letters on my pc. I am struggling to use the list of filepath details now though to deal with extracting the data I need from each of the individual databases.

I have a SQL string tested to provide me with the aggregate fitting cost from each of the databases:-

SELECT Sum(CostITEM.CostItem) AS SumOfCostItem
FROM CostITEM
WHERE (((CostITEM.Description)="Labour (Fitting)"));

I now need to read up on the automation bit utilising fso I guess for the access to the individual files. It is way beyond me at present but I will read up this weekend

 

[Bigschnoz]

It would appear that the simplest approach may be to import the necessary data into Excel then link to the main salesforecast database. I have fiddled with a macro in Excel to extract the data I require and drop it in the cell adjacent to the file path of each of the databases I need to interrogate. The macro recorded is as follows:- (Items in red require relative data references)

Sub AccessDb2()
'
' AccessDb2 Macro
' Macro recorded 08/05/2012 by Steve_Lap
'

'
Range("A2").Select
Selection.Copy
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\expserver\shared\BM\Expressions\Orders\2008\Jun\1033.mdb;DefaultDir=\\expserver\shared\BM\Expressi" _
), Array( _
"ons\Orders\2008\Jun;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("B2"))
.CommandText = Array( _
"SELECT Sum(CostITEM.CostItem) AS 'SumOfCostItem'" & Chr(13) & "" & Chr(10) & "FROM `\\expserver\shared\BM\Expressions\Orders\2008\Jun\1033`.CostITEM CostITEM" & Chr(13) & "" & Chr(10) & "WHERE (CostITEM.Description='Labour (Fabrication)')" _
)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

This gets me the data I need for the first specific database. I need to set this up as a loop to achieve the following:-

1) Ignore records where there is a value in column B of the spreadsheet against the relevent row (i.e. this data has already been imported)
2) to use the path in the cell in column A as the database target path.
3) To loop the import routine until cell A of Row 'n' is empty. (I guess there could be a more sophisticated mechanism to record which row the process has been carried out for previously but I am trying to keep it simple as it is already beyond me)

Steve

 

[VilaRestal]

Won't that mean every connection is stored in the spreadsheet? Thousands of connections to databases. I'm not sure Excel will cope with that.

 

[Bigschnoz]

hi Vilarestal
There is an option in the dataquery tool not to retain the query details, (see following post) I am hoping this does what it says on the tin and ensures that once the data has been imported into the relevent cell the query disappears and there is no overhead created.

 

[Bigschnoz]

As described above

Hopefully deselecting the highlighted boxes will remove the query overhead from the final spreadsheet.

https://docs.google.com/open?id=0B8iICXcLMCYmSXlPbzZJWlliY0U

 

[gemma-the-husky]

OP

I sent you a PM on this.

 

[Bigschnoz]

Sorted this using a bit of Excel VBA code. A bit of testing for the existence of data in cells, utilisation of ADO and a bit of SQL. Worked well with about 6 breaks where a database didnt exist. It is a bit rough and lacks finesse but worked really well


Sub TestCells()
' Main start code to set start point at A1
Dim sEndPathString As String
Dim rEndPathString As Range

Worksheets("DbImport").Range("A1").Select

ActiveCell.Name = "rEndPathString"
sEndPathString = Range("rEndPathString").Value

FilePathTest (sEndPathString)


End Sub

Sub FilePathTest(sEndPathString)

' Code to name cell where file path may be found,
'this will be concatenated with the remainder of the
'SQL instruction


Dim FilePathCellValue As String
Dim nCostItem As Name
Dim sStartPathString As String
Dim rEndPathString As Range
Dim rStartPathString As Range
Dim sMyConnect As String
Dim rMyRecordset As ADODB.Recordset
Dim sMySQL As String

On Error GoTo Failed

Do Until sEndPathString = ""

ActiveCell.Name = "rEndPathString"
sEndPathString = Range("rEndPathString").Value

If ActiveCell.Value = "" Then
GoTo Complete

Else

ActiveCell.Offset(0, 1).Select
ActiveCell.Name = "nCostItem"

End If

If ActiveCell.Value = "" Then

'Need to define ranges for start and end of string _
'start will be fixed, end will vary per entry

sStartPathString = Range("rStartPathString").Value
sEndPathString = Range("rEndPathString").Value
sMyConnect = (sStartPathString & sEndPathString)

sMySQL = "SELECT Sum(CostITEM.CostItem) AS ['SumOfCostItem']" & _
" FROM CostITEM" & _
" WHERE (CostITEM.Description= 'Labour (Fitting)');"

Set rMyRecordset = New ADODB.Recordset
rMyRecordset.Open sMySQL, sMyConnect, adOpenStatic, adLockReadOnly

Sheets("DbImport").Select
'Line below needs to point to named range representing _
'new instance of cell value range
ActiveSheet.Range("nCostItem").CopyFromRecordset rMyRecordset

If ActiveCell.Value = "" Then ActiveSheet.Range("nCostItem").Value = 0#

End If

ActiveCell.Offset(1, -1).Select

Loop

Failed:
MsgBox "Something Cocked up", vbOKOnly, "Macro Error"

Complete:
MsgBox "Its Alive", vbOKOnly, "Rock n Roll"