Delete and inport new records into an existing table

[Brian]

I have an existing table that I would like to be able to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?

 

[Joe Fallon]

It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName, strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

 

[Brian]

Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName, strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP

I have an existing table that I would like to be able to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?

.

 

[Guest]

if you have remote users, why not create an ASP page on a web application. The user can simply click a button which fires the ASP page that does a 'delete from <tablename>' and can also do the data import for that matter.

 

[Joe Fallon]

They all get written in a VBA module inside your .mdb file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP

Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName, strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP

I have an existing table that I would like to be able to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?

.

 

[Brian]

Thanks Joe,
But that would require me to send out a whole new MDB
file. Thats what I'm tring to avoid, because different
locations will have different information in the other
tables.

I need a way to load a single table within a .MDB app
from outside the app.

Thanks,
Brian

-----Original Message-----
They all get written in a VBA module inside your .mdb file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP

Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the

query
in code.

3. Then use TransferText to import the file.
4. Your import form can have text boxes to get information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName, strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP



I have an existing table that I would like to be

able
to

delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?



.

.

 

[Douglas J. Steele]

Your application should be split into a front-end (containing the queries,
forms, reports, macros and modules), linked to a back-end (containing the
tables). Only the back-end should exist on the server, with each user having
his/her own copy of the front-end, preferably on his/her hard drive. Then,
you wouldn't have to worry about impacting the data when you send a new
version of the application.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks Joe,
But that would require me to send out a whole new MDB
file. Thats what I'm tring to avoid, because different
locations will have different information in the other
tables.

I need a way to load a single table within a .MDB app
from outside the app.

Thanks,
Brian

-----Original Message-----
They all get written in a VBA module inside your .mdb file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP

Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query
in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get
information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName,
strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP



message
I have an existing table that I would like to be able
to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote
users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?



.

.

 

[Joe Fallon]

In addition to Doug's comments it would help if you outlined your
requirements in the first post, not the 3rd.
You would have got very different advice.
--
Joe Fallon
Access MVP

Thanks Joe,
But that would require me to send out a whole new MDB
file. Thats what I'm tring to avoid, because different
locations will have different information in the other
tables.

I need a way to load a single table within a .MDB app
from outside the app.

Thanks,
Brian

-----Original Message-----
They all get written in a VBA module inside your .mdb file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP

Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query
in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get
information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName,
strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP



message
I have an existing table that I would like to be able
to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote
users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?



.

.

 

[Brian]

Sorry Joe,

I suppose I did not explain the requirements very well!

The fact is, my application is not running on a server. I
have several free standing copies of the app in remote
locations (all having different data in most of the other
tables). That is the reason I'm looking to be able to
load it from something like a DOS command line (Maybe
this is not even possible using Access).

I am just attempting to try to avoid visiting locations
that were unable to load this on their own.

Sorry for the confusion.

Thanks for all your help.

Brian

-----Original Message-----
In addition to Doug's comments it would help if you outlined your
requirements in the first post, not the 3rd.
You would have got very different advice.
--
Joe Fallon
Access MVP

Thanks Joe,
But that would require me to send out a whole new MDB
file. Thats what I'm tring to avoid, because different
locations will have different information in the other
tables.

I need a way to load a single table within a .MDB app
from outside the app.

Thanks,
Brian

-----Original Message-----
They all get written in a VBA module inside your .mdb file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP



Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS

prompt
or

the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the query
in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get
information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName,
strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP



message
I have an existing table that I would like to be able
to
delete all the existing records and then import a csv
file into that empty table.

I know that I can do it by manually opening the table,
deleting the records, then doing a simple import into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote
users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?



.



.

.

 

[Joe Fallon]

Access can use the Shell command to do many tricks.
http://www.mvps.org/access
--
Joe Fallon
Access MVP

Sorry Joe,

I suppose I did not explain the requirements very well!

The fact is, my application is not running on a server. I
have several free standing copies of the app in remote
locations (all having different data in most of the other
tables). That is the reason I'm looking to be able to
load it from something like a DOS command line (Maybe
this is not even possible using Access).

I am just attempting to try to avoid visiting locations
that were unable to load this on their own.

Sorry for the confusion.

Thanks for all your help.

Brian

-----Original Message-----
In addition to Doug's comments it would help if you outlined your
requirements in the first post, not the 3rd.
You would have got very different advice.
--
Joe Fallon
Access MVP

Thanks Joe,
But that would require me to send out a whole new MDB
file. Thats what I'm tring to avoid, because different
locations will have different information in the other
tables.

I need a way to load a single table within a .MDB app
from outside the app.

Thanks,
Brian

-----Original Message-----
They all get written in a VBA module inside your .mdb
file.
Perhaps behind a button on a form.
--
Joe Fallon
Access MVP



Doesn't a DoCmd.OpenQuery need to be stored in the
database? Can these commands be run from a DOS prompt
or
the Start> Run> command line?

-----Original Message-----
It is very easy to automate.
1. Write the query to Delete all data fromn the table.
2. Use DoCmd.OpenQuery (Or RunSQL) to execute the
query
in code.
3. Then use TransferText to import the file.
4. Your import form can have text boxes to get
information like Dir and File
Name and a button to execute all this code.

ASCII Delimited files:
DoCmd.TransferText acImportDelim, strSpecName,
strTableName,
Me![txtLocalDir] & Me![txtLocalFileName], True

--
Joe Fallon
Access MVP



message
I have an existing table that I would like to be
able
to
delete all the existing records and then import a
csv
file into that empty table.

I know that I can do it by manually opening the
table,
deleting the records, then doing a simple import
into
that table.

But is there a way that I can do this automatically,
without any user interaction? I have several remote
users
that are not Access literate that I would prefer to
simplify this needed data patch. Is it possible to
automate? Or would it be better to attempt to write
explicit instructions and hope for the best?



.



.

.