Import then append

  • Thread starter Thread starter pennyjv
  • Start date Start date
P

pennyjv

I am writing a database that needs to automatically import tables from a
daily generated .mdb file, For example 20060317.mdb, then append them to a
MasterTable in my database. The structures of these tables are the same.

I am not sure how to do this, with the names (20060317.mdb) of the mdb files
changing everyday.

I am sure I will need a loop of some kind.

Any suggestions?
 
This will return the value to create your database name string:

strDbName = Format(Date,"yyyymmdd") & ".mdb"
You can then use this in the TransferDatabase method to link to the mdb and
then execute and append query that will append the data. Then use the
DeleteObject method to drop the link.
 
Thanks for the info Klatuu..
It worked fine. Now I need to write a loop, to import data over a date range.


You wouldn't know how to do this, whould you.

Pennyjv

This will return the value to create your database name string:

strDbName = Format(Date,"yyyymmdd") & ".mdb"
You can then use this in the TransferDatabase method to link to the mdb and
then execute and append query that will append the data. Then use the
DeleteObject method to drop the link.
I am writing a database that needs to automatically import tables from a
daily generated .mdb file, For example 20060317.mdb, then append them to a
Click to expand...
[quoted text clipped - 6 lines]
Any suggestions?
Click to expand...
Click to expand...
 
Yes, I know how. Before I do though, I need some info.
Are all these mdbs in the same directory?
How do you know for which dates to process?
How do you ensure you have not already imported that date?
What if dates are missing?
Do you import everything in the directory, or just within a user defined
date range?

pennyjv said:
Thanks for the info Klatuu..
It worked fine. Now I need to write a loop, to import data over a date range.


You wouldn't know how to do this, whould you.

Pennyjv

This will return the value to create your database name string:

strDbName = Format(Date,"yyyymmdd") & ".mdb"
You can then use this in the TransferDatabase method to link to the mdb and
then execute and append query that will append the data. Then use the
DeleteObject method to drop the link.
I am writing a database that needs to automatically import tables from a
daily generated .mdb file, For example 20060317.mdb, then append them to a
Click to expand...
[quoted text clipped - 6 lines]
Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Yes the mdbs are all in the same directory.

I plan on giving the user the ability to choose a start date and stop date
for the range of dates

if no stop date is given use yesterdays date.

I haven't thought about error checking or making sure that data for a
particular date has already been imported. But good idea.

If dates are missing then skip the mdb.

I just want to import one table per mdb. So if it is a 3 week cycle, I
should have 21 days worth of data.

Pennyjv


Yes, I know how. Before I do though, I need some info.
Are all these mdbs in the same directory?
How do you know for which dates to process?
How do you ensure you have not already imported that date?
What if dates are missing?
Do you import everything in the directory, or just within a user defined
date range?
Thanks for the info Klatuu..
It worked fine. Now I need to write a loop, to import data over a date range.
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
This assumes the files are named as you specified in your original post where
20060317.mdb would be today. If there are other characters before the date,
you will need to strip them off to do the compare.

This routine uses the Dir function to loop through all mdb files in the
specified directory and compare their names to the begin and end date names.
If the name is within the range, it does the transfer (I did not include that
code, but I show where it goes), if it is not, it is bypassed.
I don't know the names of the controls on your form where you enter the
start and end dates, so I made some up. You will need to change them to
match your names.
I also included a counter so the user will know How many files were imported.

Dim strStartDate as String
Dim strEndDate as String
Dim intImportCount as Integer

strStartDate = Format(Me.txtStartDate, "yyyymmdd") & ".mdb"
strEndDate = IIf IsNull(Me.txtEndDate), _
Format(DateAdd("d", -1,Date), "yyyymmdd") & ".mdb", _
Format(Me.txtEndDate, "yyyymmdd") & ".mdb")

'Path to use should be the path to the mdb files & *.mdb
'Example
'E:\WhereTheFilesAre\*.mdb

NextMdb = Dir(PathToUse, vbDirectory) ' Retrieve the first entry.
'If no mdb files are in the directory, int will return "" and the loop will
never happen
Do While NextMdb <> "" ' Start the loop.
If NextMdb >= strStartDate and NextMdb <= strEndDate Then
'Here is where you do the import
intImportCount = intImportCount + 1
End If
NextMdb = Dir ' Get next entry.
Loop

MsgBox "Imported " & Cstr(intImportCount) & " Files"

pennyjv said:
Yes the mdbs are all in the same directory.

I plan on giving the user the ability to choose a start date and stop date
for the range of dates

if no stop date is given use yesterdays date.

I haven't thought about error checking or making sure that data for a
particular date has already been imported. But good idea.

If dates are missing then skip the mdb.

I just want to import one table per mdb. So if it is a 3 week cycle, I
should have 21 days worth of data.

Pennyjv


Yes, I know how. Before I do though, I need some info.
Are all these mdbs in the same directory?
How do you know for which dates to process?
How do you ensure you have not already imported that date?
What if dates are missing?
Do you import everything in the directory, or just within a user defined
date range?
Thanks for the info Klatuu..
It worked fine. Now I need to write a loop, to import data over a date range.
Click to expand...
[quoted text clipped - 15 lines]
Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
You da man, works like a champ! Thanks
This assumes the files are named as you specified in your original post where
20060317.mdb would be today. If there are other characters before the date,
you will need to strip them off to do the compare.

This routine uses the Dir function to loop through all mdb files in the
specified directory and compare their names to the begin and end date names.
If the name is within the range, it does the transfer (I did not include that
code, but I show where it goes), if it is not, it is bypassed.
I don't know the names of the controls on your form where you enter the
start and end dates, so I made some up. You will need to change them to
match your names.
I also included a counter so the user will know How many files were imported.

Dim strStartDate as String
Dim strEndDate as String
Dim intImportCount as Integer

strStartDate = Format(Me.txtStartDate, "yyyymmdd") & ".mdb"
strEndDate = IIf IsNull(Me.txtEndDate), _
Format(DateAdd("d", -1,Date), "yyyymmdd") & ".mdb", _
Format(Me.txtEndDate, "yyyymmdd") & ".mdb")

'Path to use should be the path to the mdb files & *.mdb
'Example
'E:\WhereTheFilesAre\*.mdb

NextMdb = Dir(PathToUse, vbDirectory) ' Retrieve the first entry.
'If no mdb files are in the directory, int will return "" and the loop will
never happen
Do While NextMdb <> "" ' Start the loop.
If NextMdb >= strStartDate and NextMdb <= strEndDate Then
'Here is where you do the import
intImportCount = intImportCount + 1
End If
NextMdb = Dir ' Get next entry.
Loop

MsgBox "Imported " & Cstr(intImportCount) & " Files"
Yes the mdbs are all in the same directory.
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
Back
Top