Automate an import that prompts for the destination

[CarlyS]

I am looking for some advice...

I am making a database for school programs that will receive lots of data every day. They will use one copy of the database for each classroom. I am worried that they will become too large very quickly because of the large amounts of data being entered daily, so I would like to recommend that at the end of the school year that database is saved in an archive folder and a new database is begun.

There will be a small amount of data that will cross over between school years and it is guaranteed that my teachers will gripe if they have to type it in all over again.

I would like to enable them to import data from a few tables into identical tables in a new database each year. Ideally, there would be a button that will prompt them for the name/location of their old database in a way where they can browse and select. Is this possible? (And is it difficult?--I'm still learning).

Thanks to anyone who can offer some wisdom!
Carly

 

[Taff]

Hi CarlyS,

I have attached a small example. Have a look at the OnClick event of the command button.

Taff.

 

[CarlyS]

Perhaps I mean "append"

Hi Taff,
Thank you so much. That is a lovely example of exactly what I am talking about in terms of selecting the database to import from.

Two things:
1. I suppose I am using the wrong word. I actually want to append data from tables in the old database to tables in the new database.

2. While I want them to be able to specify the old database in the manner that your example allows the user to do, I don't want them to have to select the tables. I would like to have that part already set up.

Basically, Click a button, be prompted for the old database (like your code does), and then the data (that I've already specified) automatically is appended to the tables in the new database.

Does anyone know how to do this?

Many thanks,
Carly

 

[Taff]

Hi Carly,

Will the name of the tables you want to append from change?

Taff

 

[CarlyS]

No, the table names will never change.

 

[Taff]

Hi Carly,

Maybe this will help you get on the right path.

Taff.

 

[biskra]

try with my sample .
sorry for my bad english.

 

[CarlyS]

Thank you both so much for your replies. I see how this works in your sample, Taff, but when I try it in my database I get this error message:

"An expression you entered is the wrong data type for one of the arguments."

Any idea what this could mean?

I appreciate any ideas someone might have. The error message happened after the msgbox but before the box pops up prompting for the database name/location.

 

[Taff]

Hi Carly,

Make sure you have copied the module over to your database as well and hopefully that should work.

Ant.

 

[CarlyS]

That was it! How silly of me. Thank you so much for your help. This works beautifully!

 

[Taff]

Glad to help.