Brian said:Two ways:
1. Create a RunSQL macro. The SQL statement will be this:
Delete * from <YourTableName>
2. Create a new query. Select the table. Change the query type to Delete.
Save the query and then create a macro that uses the OpenQuery action to call
the query's name.
Note that, either way, it will pop up a warning telling you how many records
it will delete. In a macro, you can do the SetWarnings -> No before the
SQL/query, then SetWarnings -> Yes after the SQL/queryIn VBA (e.g. from the
click of a button on a form), your code would look something like this:
Private Sub Button1_Click
DoCmd.SetWarnings False 'disables the warning message
DoCmd.RunSQL "Delete * from [All FANs on P2]" 'delete the existing records
DoCmd.SetWarnings True 'turns warnings back on
DoCmd.TransferSpreadsheet acImport, , "All FANs on P2", strFERS &
"P2_Raw_Dis.xls", True 'import new records
End Sub
Also, I would avoid spaces in table names where possible (i.e. "All FANs on
P2"). It ends up requiring special treatment such as the square brackets,
where one could normally just enter the table name, because the syntax will
otherwise look at the second word in the name as though it is another
command, not part of the name.
I would instead use something like P2FANs
Richard said:Brian,
Thanks for you patience on this.
From you list, this is the option I need:
"As long as the structure of the Excel sheet does not change between imports
(i.e. same type of data in the same columns with the same number of columns),
you can always run a delete query to flush the old data from the table
between imports. That way, each time you import from the spreadsheet, you get
a completely new set of data to replace the old set, replete with new primary
keys for each entry, starting after the last primary key previously used
(unless you delete all records from the table and then compact/repair the
database, which resets the AutoNumber back to 1.)"
So my next (and hopefully last) question: How do i "run a delete query to
flush the old data" from within a macro?
Richard said:Brian,
I think we are getting very close; I just get this error on the import
command:
Fun-Time error '2391':
Field 'Project_No' doesn't exist in destinationo table 'shmerge_funding.'
Code is:
Sub testDeleteReloadTable2()
Dim strFERS
Dim oShell, oSpecialFolders
Set oShell = CreateObject("WScript.Shell")
Set oSpecialFolders = oShell.SpecialFolders
strFERS = oSpecialFolders("MyDocuments") & "\Discoverer\Like Wally Audit
Reports\"
DoCmd.RunSQL "Delete * from [shmerge_funding]" 'delete the existing records
DoCmd.TransferSpreadsheet acImport, , "shmerge_funding", strFERS &
"P2_Raw_Dis.xls", True
End Sub
I get error on last DoCmd line. I tried two different tables and get same
error.
--
Richard
Brian said:Two ways:
1. Create a RunSQL macro. The SQL statement will be this:
Delete * from <YourTableName>
2. Create a new query. Select the table. Change the query type to Delete.
Save the query and then create a macro that uses the OpenQuery action to call
the query's name.
Note that, either way, it will pop up a warning telling you how many records
it will delete. In a macro, you can do the SetWarnings -> No before the
SQL/query, then SetWarnings -> Yes after the SQL/queryIn VBA (e.g. from the
click of a button on a form), your code would look something like this:
Private Sub Button1_Click
DoCmd.SetWarnings False 'disables the warning message
DoCmd.RunSQL "Delete * from [All FANs on P2]" 'delete the existing records
DoCmd.SetWarnings True 'turns warnings back on
DoCmd.TransferSpreadsheet acImport, , "All FANs on P2", strFERS &
"P2_Raw_Dis.xls", True 'import new records
End Sub
Also, I would avoid spaces in table names where possible (i.e. "All FANs on
P2"). It ends up requiring special treatment such as the square brackets,
where one could normally just enter the table name, because the syntax will
otherwise look at the second word in the name as though it is another
command, not part of the name.
I would instead use something like P2FANs
Richard said:Brian,
Thanks for you patience on this.
From you list, this is the option I need:
"As long as the structure of the Excel sheet does not change between imports
(i.e. same type of data in the same columns with the same number of columns),
you can always run a delete query to flush the old data from the table
between imports. That way, each time you import from the spreadsheet, you get
a completely new set of data to replace the old set, replete with new primary
keys for each entry, starting after the last primary key previously used
(unless you delete all records from the table and then compact/repair the
database, which resets the AutoNumber back to 1.)"
So my next (and hopefully last) question: How do i "run a delete query to
flush the old data" from within a macro?
--
Richard
:
The way I have described will add new rows, each with a unique PK, every time
you import the spreadsheet. It will never update existing rows. If the
spreadsheet has 100 rows at the first import, it will import 100 rows, with
the PK's being 1 through 100. If you then add rows to the spreadsheet so that
the next import has 125 rows, it will import them all as new records,
assigning PK's 101 through 225. There will now be 225 records in the table.
I cannot tell from any of your posts if that is what you want or if it
something else, so here are a couple more ideas to cover contingencies:
As long as the structure of the Excel sheet does not change between imports
(i.e. same type of data in the same columns with the same number of columns),
you can always run a delete query to flush the old data from the table
between imports. That way, each time you import from the spreadsheet, you get
a completely new set of data to replace the old set, replete with new primary
keys for each entry, starting after the last primary key previously used
(unless you delete all records from the table and then compact/repair the
database, which resets the AutoNumber back to 1.)
Now, if you want it to only add rows that were not there before, then it is
more complex because you must have a way for the system to uniquely identify
each row in the spreadsheet and map it to a single record in the table. In
short, the spreadsheet must have a unique key that is propagated into the
table.
In this case, you can use two tables: one that stores the permanent data
(e.g. tblPerm) and another that holds data temporarily for import filtering
(e.g. tblTemp).
1. Delete all records from tblTemp.
2. Import spreadsheet to tblTemp
3. Run a query to delete anything in TableTemp that should not be in tblPerm
4. Append what is left to tblPerm.
5. Delete all records from tblTemp
This does, of course, assume that you have some way of identifying which row
in the spreadsheet matches the record in tblPerm.
If, instead, what you want is a way to import different spreadsheet formats
(i.e. the format and/or number of columns is not known before the import,
then the import will always create a new table without a PK. You can just add
VBA code to create the AutoNumber PK field in the newly-created table after
the import code.
:
Mike,
You are correct. Following your steps to the letter does keep the PK.
However, it also keeps the old data. I need to be able to update the Excel
file, and then import it as is (not all it to existing rows), only add new
PKs.
Sorry if I wasn't clear before.
--
Richard
:
The import will not add any new columns. It will, however, leave your
newly-created AutoNumber/PK field in place. Are you saying that the next
import deletes the AutoNumber field you just added to the table structure?
Let me try to re-word this as steps:
1. Import the text once. This creates the table.
2. Open the newly-created table in design view.
3. Insert a new, AutoNumber field to the table and make the new field the
primary key.
4. Save the table design.
5. Do NOT delete the table.
6. Import more data to the SAME table using the TransferText method you
described in your original post.
If you do any of the things below, it will undo/bypass the critical setup:
1. Neglect to add the AutoNumber, Primary Key field to the table after the
initial import.
2. Save the table design with the AutoNumber Primary Key field.
3. Import the text file to a different table.
4. Delete the table between imports.
:
Brian,
Sorry for the row vs column mistake. I meant to say "It doesn't add any new
columns, including a PC column".
I still can't get it to work. I can originally get a PC column inserted in
two ways: (1.) select "Let Access add primary key' during manual inport or
(2.) manual input with "No primary Key" selected, but then adding the PK in
design mode (as you suggested).
The problem is, when I later import new data with a macro (same number of
columns, and same column headings), the newly imported table has no PC column.
--
Richard
:
It should not add a new row. The Primary Key is a column. The AutoNumber
field will be auto-populated as new rows are added to the table. If the
number of columns in the incoming spreadsheet is different each time, then
look at the bottom of this post; otherwise, keep reading.
Here's an example of how it should work. Let's say we have a spreadsheet
called Invoices having two columns: Customer & InvoiceDate.
An initial, manual import in Access (File -> Get External Data -> Import) of
the spreadsheet will result in a table called Invoices. It will have two
fields: Customer & InvoiceDate.
Now we go to the new Invoice table in design view and add a new AutoNumber
field called InvoiceID. Set the InvoiceID field as the primary key. Save the
table design.
Two things are guaranteed at this point.
1. The existing rows in the Invoices table will each have an auto-assigned
InvoiceID.
2. Any new row(s) inserted into the table, regardless of how this is
done--manually (typed in), imported manually (File -> Get External Data ->
Import), or inserted programmatically--will get an InvoiceID auto-assigned at
the time of insertion.
Now, if you are instead a) deleting the table between imports or b)
importing differently-formatted spreadsheets new tables each time, then that
is a different matter. Are you importing a variety of spreadsheet formats, or
do you repeatedly import from the same format? From the code you posted, it
appeared that you were importing the same spreadsheet format to the same
table each time, since the name is hard-coded.
If the spreadsheet has a different number of columns each time it is
imported, then you may need to replace the table or create a new table with
each import. In this case, you can add code to create the AutoNumber Primary
Key to the newly-created table after the import creates it. Post back for
more specifics if this is the case.
:
Brian,
The programmatic import just gets the data in the Excel spreadsheet. It
doesn't add any new rows, including a PC row.
--
Richard
:
PK is shorthand for Primary Key.
Assuming that the first, manual, import, establishes the table structure and
that you have then manually added an AutoNumber field as the Primary Key of
the new table, what entries do you get in the AutoNumber field as you then
programmatically import new batches of data from the spreadsheet?
:
Brian,
I have already manually imported the files, setting the switch to AutoNumber.
Like you, I though future programmed imports would then incorporate an
AutoNumber based on the file that is created during the manual import
process. However, it didn't work that way.
That's why I'm thinking that there must be a switch to set during the
programmed imports that will guarantee Autonumbering.
P.S. I don't know what you mean by "PK".
--
Richard
:
Run the code once to establish the table structure (or do it manually). Then
add an AutoNumber field to the table and set it as the PK. Future imports
will import to the additional fields, and the PK will increment automatically.
:
How can I assure that Access adds a primary key to my imported Excel sheets?
I'm currently using this command structure:
DoCmd.TransferSpreadsheet acImport, , "All FANs on P2", strFERS &
"P2_Raw_Dis.xls", True
Brian said:1. Does it work when you import the same thing manually?
2. Is there a field called Project_No in the spreadsheet but not in the
relevant table?
Richard said:Brian,
I think we are getting very close; I just get this error on the import
command:
Fun-Time error '2391':
Field 'Project_No' doesn't exist in destinationo table 'shmerge_funding.'
Code is:
Sub testDeleteReloadTable2()
Dim strFERS
Dim oShell, oSpecialFolders
Set oShell = CreateObject("WScript.Shell")
Set oSpecialFolders = oShell.SpecialFolders
strFERS = oSpecialFolders("MyDocuments") & "\Discoverer\Like Wally Audit
Reports\"
DoCmd.RunSQL "Delete * from [shmerge_funding]" 'delete the existing records
DoCmd.TransferSpreadsheet acImport, , "shmerge_funding", strFERS &
"P2_Raw_Dis.xls", True
End Sub
I get error on last DoCmd line. I tried two different tables and get same
error.
--
Richard
Brian said:Two ways:
1. Create a RunSQL macro. The SQL statement will be this:
Delete * from <YourTableName>
2. Create a new query. Select the table. Change the query type to Delete.
Save the query and then create a macro that uses the OpenQuery action to call
the query's name.
Note that, either way, it will pop up a warning telling you how many records
it will delete. In a macro, you can do the SetWarnings -> No before the
SQL/query, then SetWarnings -> Yes after the SQL/queryIn VBA (e.g. from the
click of a button on a form), your code would look something like this:
Private Sub Button1_Click
DoCmd.SetWarnings False 'disables the warning message
DoCmd.RunSQL "Delete * from [All FANs on P2]" 'delete the existing records
DoCmd.SetWarnings True 'turns warnings back on
DoCmd.TransferSpreadsheet acImport, , "All FANs on P2", strFERS &
"P2_Raw_Dis.xls", True 'import new records
End Sub
Also, I would avoid spaces in table names where possible (i.e. "All FANs on
P2"). It ends up requiring special treatment such as the square brackets,
where one could normally just enter the table name, because the syntax will
otherwise look at the second word in the name as though it is another
command, not part of the name.
I would instead use something like P2FANs
:
Brian,
Thanks for you patience on this.
From you list, this is the option I need:
"As long as the structure of the Excel sheet does not change between imports
(i.e. same type of data in the same columns with the same number of columns),
you can always run a delete query to flush the old data from the table
between imports. That way, each time you import from the spreadsheet, you get
a completely new set of data to replace the old set, replete with new primary
keys for each entry, starting after the last primary key previously used
(unless you delete all records from the table and then compact/repair the
database, which resets the AutoNumber back to 1.)"
So my next (and hopefully last) question: How do i "run a delete query to
flush the old data" from within a macro?
--
Richard
:
The way I have described will add new rows, each with a unique PK, every time
you import the spreadsheet. It will never update existing rows. If the
spreadsheet has 100 rows at the first import, it will import 100 rows, with
the PK's being 1 through 100. If you then add rows to the spreadsheet so that
the next import has 125 rows, it will import them all as new records,
assigning PK's 101 through 225. There will now be 225 records in the table.
I cannot tell from any of your posts if that is what you want or if it
something else, so here are a couple more ideas to cover contingencies:
As long as the structure of the Excel sheet does not change between imports
(i.e. same type of data in the same columns with the same number of columns),
you can always run a delete query to flush the old data from the table
between imports. That way, each time you import from the spreadsheet, you get
a completely new set of data to replace the old set, replete with new primary
keys for each entry, starting after the last primary key previously used
(unless you delete all records from the table and then compact/repair the
database, which resets the AutoNumber back to 1.)
Now, if you want it to only add rows that were not there before, then it is
more complex because you must have a way for the system to uniquely identify
each row in the spreadsheet and map it to a single record in the table. In
short, the spreadsheet must have a unique key that is propagated into the
table.
In this case, you can use two tables: one that stores the permanent data
(e.g. tblPerm) and another that holds data temporarily for import filtering
(e.g. tblTemp).
1. Delete all records from tblTemp.
2. Import spreadsheet to tblTemp
3. Run a query to delete anything in TableTemp that should not be in tblPerm
4. Append what is left to tblPerm.
5. Delete all records from tblTemp
This does, of course, assume that you have some way of identifying which row
in the spreadsheet matches the record in tblPerm.
If, instead, what you want is a way to import different spreadsheet formats
(i.e. the format and/or number of columns is not known before the import,
then the import will always create a new table without a PK. You can just add
VBA code to create the AutoNumber PK field in the newly-created table after
the import code.
:
Mike,
You are correct. Following your steps to the letter does keep the PK.
However, it also keeps the old data. I need to be able to update the Excel
file, and then import it as is (not all it to existing rows), only add new
PKs.
Sorry if I wasn't clear before.
--
Richard
:
The import will not add any new columns. It will, however, leave your
newly-created AutoNumber/PK field in place. Are you saying that the next
import deletes the AutoNumber field you just added to the table structure?
Let me try to re-word this as steps:
1. Import the text once. This creates the table.
2. Open the newly-created table in design view.
3. Insert a new, AutoNumber field to the table and make the new field the
primary key.
4. Save the table design.
5. Do NOT delete the table.
6. Import more data to the SAME table using the TransferText method you
described in your original post.
If you do any of the things below, it will undo/bypass the critical setup:
1. Neglect to add the AutoNumber, Primary Key field to the table after the
initial import.
2. Save the table design with the AutoNumber Primary Key field.
3. Import the text file to a different table.
4. Delete the table between imports.
:
Brian,
Sorry for the row vs column mistake. I meant to say "It doesn't add any new
columns, including a PC column".
I still can't get it to work. I can originally get a PC column inserted in
two ways: (1.) select "Let Access add primary key' during manual inport or
(2.) manual input with "No primary Key" selected, but then adding the PK in
design mode (as you suggested).
The problem is, when I later import new data with a macro (same number of
columns, and same column headings), the newly imported table has no PC column.
--
Richard
:
It should not add a new row. The Primary Key is a column. The AutoNumber
field will be auto-populated as new rows are added to the table. If the
number of columns in the incoming spreadsheet is different each time, then
look at the bottom of this post; otherwise, keep reading.
Here's an example of how it should work. Let's say we have a spreadsheet
called Invoices having two columns: Customer & InvoiceDate.
An initial, manual import in Access (File -> Get External Data -> Import) of
the spreadsheet will result in a table called Invoices. It will have two
fields: Customer & InvoiceDate.
Now we go to the new Invoice table in design view and add a new AutoNumber
field called InvoiceID. Set the InvoiceID field as the primary key. Save the
table design.
Two things are guaranteed at this point.
1. The existing rows in the Invoices table will each have an auto-assigned
InvoiceID.
2. Any new row(s) inserted into the table, regardless of how this is
done--manually (typed in), imported manually (File -> Get External Data ->
Import), or inserted programmatically--will get an InvoiceID auto-assigned at
the time of insertion.
Now, if you are instead a) deleting the table between imports or b)
importing differently-formatted spreadsheets new tables each time, then that
is a different matter. Are you importing a variety of spreadsheet formats, or
do you repeatedly import from the same format? From the code you posted, it
appeared that you were importing the same spreadsheet format to the same
table each time, since the name is hard-coded.
If the spreadsheet has a different number of columns each time it is
imported, then you may need to replace the table or create a new table with
each import. In this case, you can add code to create the AutoNumber Primary
Key to the newly-created table after the import creates it. Post back for
more specifics if this is the case.
:
Brian,
The programmatic import just gets the data in the Excel spreadsheet. It
doesn't add any new rows, including a PC row.
--
Richard
:
PK is shorthand for Primary Key.
Assuming that the first, manual, import, establishes the table structure and
that you have then manually added an AutoNumber field as the Primary Key of
the new table, what entries do you get in the AutoNumber field as you then
programmatically import new batches of data from the spreadsheet?
:
Brian,
I have already manually imported the files, setting the switch to AutoNumber.
Like you, I though future programmed imports would then incorporate an
AutoNumber based on the file that is created during the manual import
process. However, it didn't work that way.
That's why I'm thinking that there must be a switch to set during the
programmed imports that will guarantee Autonumbering.
P.S. I don't know what you mean by "PK".
--
Richard
:
Run the code once to establish the table structure (or do it manually). Then
add an AutoNumber field to the table and set it as the PK. Future imports
will import to the additional fields, and the PK will increment automatically.
:
How can I assure that Access adds a primary key to my imported Excel sheets?
I'm currently using this command structure:
DoCmd.TransferSpreadsheet acImport, , "All FANs on P2", strFERS &
"P2_Raw_Dis.xls", True
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.