Save Archive Data To Dynamic Location 1

[SgtJarrow]

Let's start with the details:

Access 2000, prefer to work with ADO objets, but will use DAO if necessary
Monthly processing of approximately 300K lines = to approx 100Mb of data
Already have dynamic strucure in place, i.e. the db creates the appropriate folders and creates a blank archive.mdb file for archiving.

What I need:
At the start of my processing, I delete last month's data out and grab this months from the SQL server. Management wants me to store a local copy of the data as we don't have access to the server data except for the current month. I decided it would be better to store each month's data in it's own smaller database. I need to open a connection to this new, blank database and create a table there with all the data in my master db.

My DAO coding skills are great, but I am trying to learn ADO and therefore really want to accomplish this through ADO. I am just having a bear of a time getting the code started....Anyone care to shove me (off the cliff, please!) in the right direction? Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[chrissie1]

the database exists?
the table you make like this

docmd.runsql "SELECT into database.dbo.newtable FROM database.dbo.oldtable Where month = previousmonth"

or something like this

database is the name of the database you refer to
if its on another server then you need to put the servername in front of it.
dont forget select into can only be done on a target table that does not yet exists Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[SgtJarrow]

Chrissie,

I thank you for your post....I decided to use something very similar.....I created a simple make-take SQL statement and just set the path to the current archive database as a variable....works like a charm.

Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com