How to renew ACCESS data from EXCEL

[Martin \(Martin Lee\)]

How to renew ACCESS data from EXCEL

I have to renew ACCESS data according to a EXCEL file sent to me every week.

the EXCEL file is called bankdata.xls which have only one sheet.


The structure of the EXCEL file is

[FILE NO] [CUSTOMER NAME] [AMOUNT BALANCE] [OVERDUE BALANCE]
[OVERDUE DATE]

00102 Martin Lee 200,000
0 - -
00103 Harry Poter 150,000 3000
2006-11-15
00106 Jane Wang 143,000 2900
2006-11-13


The ACCESS QUERY structure is: <<< Please take in mind, it is a QUERY>>>

[FILE NO] [ADDRESS] [OVERDUE BALANCE] [OVERDUE DATE]

00102
00103
00104
00105
00106
00107

Please be noticed that the [FILE NO] is the PRIMARY KEY of the ACCESS and
the EXCEL. However, the ACCESS [FILE NO] is more than the EXCEL [FILE NO]


QUESTION:

I want to renew the ACCESS data from the EXCEL, preferably by clicking a
BUTTON. How to achieve this?
Please also be noticed that the [OVERDUE DATE] with " - - " should be put
into ACCESS as BLANK(nothing in it)


Thank you!

Yours Sincerely,

Martin LEE
2006-11-22

 

[Guest]

Hi Martin,

I have to renew ACCESS data according to a EXCEL file sent to me every week.

Do you need to "clean the slate", ie. delete all existing records in your
table, before importing new records? Or, do you need to just add new records
to your table from weekly feeds of your Excel spreadsheet?

The ACCESS QUERY structure is: <<< Please take in mind, it is a QUERY>>>

[FILE NO] [ADDRESS] [OVERDUE BALANCE] [OVERDUE DATE]

Where are you picking up the Address data?

Please be noticed that the [FILE NO] is the PRIMARY KEY of the ACCESS and
the EXCEL.

While the same identifier may be present in Access and Excel, Excel does not
have primary keys, per say.

However, the ACCESS [FILE NO] is more than the EXCEL [FILE NO]

I don't understand what you are saying here.

I want to renew the ACCESS data from the EXCEL, preferably by clicking a
BUTTON. How to achieve this?

Link the spreadsheet file. Create an Append query that uses this linked
table as a source of data, and appends the records to the target table. Once
you get the query working the way it should, you can then run the same query
from VBA code, without the usual warning messages. For example, you could do
something like this:

To first clear the table of records, if required:

Dim strSQL As String

strSQL = "DELETE * FROM TableName"
CurrentDb.Execute strSQL, dbFailOnError

where TableName is the name of your table.

To append new records:
strSQL = "Your valid SQL statement goes here for an append query"
CurrentDb.Execute strSQL, dbFailOnError

or
CurrentDb.Execute "SavedQuery", dbFailOnError

where SavedQuery is the name of the append query. Remove the optional
dbFailOnError if you are not first clearing all records from the table. This
will prevent an error in the attempt that you try appending a [FILE NO] value
that already exists in the table. The attempt will just fail silently for any
matching [FILE NO] records, while appending all non-matching records.

Please also be noticed that the [OVERDUE DATE] with " - - " should be put
into ACCESS as BLANK(nothing in it)

A Date / Time data type can be null, as long as the required property is No.
However, it cannot include a string value like " - - ". You would need a
Text data type in that case. I recommend sticking with the data / time data
type.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

How to renew ACCESS data from EXCEL

I have to renew ACCESS data according to a EXCEL file sent to me every week.

the EXCEL file is called bankdata.xls which have only one sheet.


The structure of the EXCEL file is

[FILE NO] [CUSTOMER NAME] [AMOUNT BALANCE] [OVERDUE BALANCE]
[OVERDUE DATE]

00102 Martin Lee 200,000
0 - -
00103 Harry Poter 150,000 3000
2006-11-15
00106 Jane Wang 143,000 2900
2006-11-13


The ACCESS QUERY structure is: <<< Please take in mind, it is a QUERY>>>

[FILE NO] [ADDRESS] [OVERDUE BALANCE] [OVERDUE DATE]

00102
00103
00104
00105
00106
00107

Please be noticed that the [FILE NO] is the PRIMARY KEY of the ACCESS and
the EXCEL. However, the ACCESS [FILE NO] is more than the EXCEL [FILE NO]


QUESTION:

I want to renew the ACCESS data from the EXCEL, preferably by clicking a
BUTTON. How to achieve this?
Please also be noticed that the [OVERDUE DATE] with " - - " should be put
into ACCESS as BLANK(nothing in it)


Thank you!

Yours Sincerely,

Martin LEE
2006-11-22