Update Query using Excel data as input

[Linda1485]

How can I write an update query using excel data as input to the query? What
I would like to do is replace the record in the Access table with the record
from the Excel spreadsheet. Each record in the spreadsheet has in it all of
the fields from the Access tables, but some of the data has been changed.
The key to the table has not been changed.

What is the best way to get the table updated with the changed data? I want
to do this automatically, not key manually.

The reason I have data in Excel is because I have several people making
changes to the database and they don't have access. Access is our data
repository and reporting tool.

Thanks,

Linda

 

[fredg]

How can I write an update query using excel data as input to the query? What
I would like to do is replace the record in the Access table with the record
from the Excel spreadsheet. Each record in the spreadsheet has in it all of
the fields from the Access tables, but some of the data has been changed.
The key to the table has not been changed.

What is the best way to get the table updated with the changed data? I want
to do this automatically, not key manually.

The reason I have data in Excel is because I have several people making
changes to the database and they don't have access. Access is our data
repository and reporting tool.

Thanks,

Linda

You can link Access to the Excel table.
File + Get External Data + Link
Navigate to the spreadsheet table.

Then you can use this linked table in a query.

Note: Excel is a flat database, Access is a relational database.
How will it help to have flat data used directly in an Access table?

 

[Linda1485]

Note: Excel is a flat database, Access is a relational database.
How will it help to have flat data used directly in an Access table?

HI Fred,

Thanks for your response. I did end up figuring out how to do an update
query using the data from the excel spreadsheet. The excel spreadsheet was
created by exporting an access table and then having someone who doesn't have
Access make changes to the spreadsheet. I needed to create an update query
that set each of the Access table's fields equal to the fields imported from
the excel spreadsheet.

Thank you for your response,
Linda

 

[gllincoln]

Hi Linda,

There are several ways to do this - what is the best way, depends on how the
data travels, whether you have multiple spreadsheets to import the changed
data from, or one spreadsheet that gets multiple changes made by multiple
users, and your skill level.

If you have a single spreadsheet copy to work with - then linking to the
spreadsheet and running an update query against the linked data might be the
best way to go.

If you have multiple copies of the same spreadsheet that different users are
using and they are updating different records, then you might want to
include a date/time stamp in the Access table, and set up the Excel sheet so
that field is updated if the user changes information in the row. That way,
you could import the copies of the spreadsheet into a temp table - and
update all records where the temp date/time stamp was newer than the Access
permanent table.

Assuming you are using Access 2007, for a moment, probably the best way to
go is a bit more complicated but would provide a lot more benefit to you and
your organization down the road. You should consider building a data input
front end version of the program with a backend database (the tables)
residing on the server. Then distribute your front end application to your
users along with the runtime version of Access. The key here is that the
runtime of Access 2007 is a free download, where the earlier versions
required that you purchase the developer's edition of Access to get the
runtime module. The runtime module allows you to use existing Access
applications, but not create new Access databases objects. There are
significant differences between putting together an application that is
going to run on full-blown Access and building an application that will
operate with the runtime module but in your situation, the benefits might be
worth the effort.

Hope this helps,
Gordon

 

[Linda1485]

Gordon, thank you for your response.

Do the users who are running the front end need to be connected to the
server where the backend database resides? We don't have a server for users
to connect to. We are a group of people working out of our homes all over
California. Hence the need to use Excel. This is a fledgling operation and
I am cobbling together this application from the web! I downloaded the
Projects template and modified it.

I am just glad I was able to get the update query to work. I had a little
trouble with fields defined not to accept zero-length-data. I finally
changed that setting to Yes, so that blank fields from excel would be
accepted.

Thanks again.

Linda

 

[Clif McIrvin]

I am just glad I was able to get the update query to work. I had a
little
trouble with fields defined not to accept zero-length-data. I finally
changed that setting to Yes, so that blank fields from excel would be
accepted.

Linda, I'd be wary of using zero length strings. I've discovered the
hard way that it's much easier to deal with null values than ZLS. In my
experience, empty Excel cells tend to come over as null values, not ZLS.

 

[Linda1485]

HI Clif,

For some reason, I had a heck of a time with blanks in the excel
spreadsheet, until I changed the access text fields to accept zls. I even
tried cleaning up the spreadsheet, putting blanks into the blank cells, which
were defined as text. I have no idea what is really in those "blank" cells,
nulls or ?? Is there a way to find out?

I'm still learning, too....2007

Linda

 

[Clif McIrvin]

HI Clif,

For some reason, I had a heck of a time with blanks in the excel
spreadsheet, until I changed the access text fields to accept zls. I
even
tried cleaning up the spreadsheet, putting blanks into the blank
cells, which
were defined as text. I have no idea what is really in those "blank"
cells,
nulls or ?? Is there a way to find out?

One way: from the immediate window (Excel VBA) when you have the
worksheet in question open enter

?isempty(range("A1"))

assuming A1 is one of your 'blank' cells. If it returns True, the cell
'should' come into Access as a null (at least, I think so in 2003.)

I may have mis-spoke. I just looked at my table definitions (should have
first, huh?) and see that I have allow zero length = yes (and Required =
no). Where I ran into trouble w/ zls was in a different table -- and it
was some of my VBA logic that choked on the zls -- in that case it was
easier to remove the zls and force a null -- but again, that wasn't
dealing with Excel.

Now that you've asked, I'll have to do a bit of digging and see what I
am actually getting from my linked worksheet (tomorrow, though.) In my
case I'm the only user of the Excel sheet, and after some trial and
error I ended up with a linked table pointing to that worksheet and a
series of select / update and select / append queries to move the
appropriate data into the actual Access table. (Someday that Excel
worksheet is supposed to be repalced with Access tables and forms....)

I'm still learning, too....2007

Don't have access to 2007 at present.

 

[Clif McIrvin]

HI Clif,

For some reason, I had a heck of a time with blanks in the excel
spreadsheet, until I changed the access text fields to accept zls. I
even
tried cleaning up the spreadsheet, putting blanks into the blank
cells, which
were defined as text. I have no idea what is really in those "blank"
cells,
nulls or ?? Is there a way to find out?

Hi Linda

Here's a bit of code I threw together to look at my worksheet. Open
your worksheet, select a cell inside your data region and run this sub
from the VBA editor.

If you don't already have a VBA code module in Excel create a new module
then paste this into it:

Public Sub xxx()
Dim c As Range
For Each c In ActiveCell.EntireRow.Range(Cells(1), Cells(72))
Debug.Print c.Column; c.NumberFormat; c.Value; IsEmpty(c)
Next c
Set c = Nothing
End Sub

My worksheet happens to be 72 columns wide - change the constants as
needed.
Most of my columns are "General" format, I have a few text columns (the
numberformat shows up as @ ). It seems that all my blank cells are in
fact Empty, and they show up in the linked table in Access as Null --
there are no zero length strings in my linked table.

I'd guess (emphasis on guess) that a) when you created the worksheet
originally you inadvertantly created the zls (is that even possible? )or
b) you're seeing something different between 2003 and 2007.

Here's an abbreviated sample of my output (In the immediate window):

<output>
32 General 8.15 False
33 GeneralCMFalse
34 dd-mmm hh:mm1/9/2007 False
35 @ 7 False
36 0.00%True
37 0 3627.65298427063 False
65 General 70720 False
66 0 5595 False
67 GeneralTrue
68 @True
69 @True
70 @True
71 @True
72 @True

</output>

HTH!