Update query assistance

[The Last Mimsy]

Hi guys,

Access Novice here...

I have a 30MB database that is so large that it is spread across three
workbook tabs (120k records plus by 20 cells wide).

I did two things. I split the spread sheet up into ID field and unique
data, then use another tab (page) for another data set of the sheet.
Similar to relation database table structuring.

I get the whole thing onto a single sheet this way (20 page tabs wide).

Ok, now my database is several months old. I just DLd the new sheet.

I want to construct a series of queries in access that will update any
records that have changed data, and add any new records, but I have had
fairly poor luck constructing queries in access due to my unfamiliarity
with it. My last database authoring session was back in the Borland
Paradox days.

Is there a simple query against a sheet with many cells width that I
can use to update a series of pages that carry only two cells, as in an
ID field and a data field?

Would it be better to run my query for the new record add, and another
query to update the changed records?

 

[Maury Markowitz]

  Would it be better to run my query for the new record add, and another
query to update the changed records?

Yes and no. The total amount of work ends up being the same, but it
might be much easier to read.

The first thing to do is look for new records. Loop over the column
with the unique ID's and do a query on each one to see if it exists.
If it does not, put in a placeholder row with no additional
information.

Now do a second loop over the data, and this time copy each of the
fields from the spreadsheet into the right column in the database.
Don't worry about checking to see if it actually changed, because ADO/
DAO will only write if the new data is different than what you used to
have.

It's not a clean solution, nor fast, but that should do the trick.

Once this is working I would HIGHLY recommend you reverse the process
and use Access as the real data and copy it into Excel when you need
it.

Maury

 

[The Last Mimsy]

Yes and no. The total amount of work ends up being the same, but it
might be much easier to read.

The first thing to do is look for new records. Loop over the column
with the unique ID's and do a query on each one to see if it exists.
If it does not, put in a placeholder row with no additional
information.

Now do a second loop over the data, and this time copy each of the
fields from the spreadsheet into the right column in the database.
Don't worry about checking to see if it actually changed, because ADO/
DAO will only write if the new data is different than what you used to
have.

It's not a clean solution, nor fast, but that should do the trick.

Once this is working I would HIGHLY recommend you reverse the process
and use Access as the real data and copy it into Excel when you need
it.

Maury

Well, my next step is to go over the data and mark all of the titles
which I own. Then I will query the whole thing and compile a set of
tables that only carry my titles. I'll make that one in access maybe.

I just like my front end I built for the spreadsheet.

http://i255.photobucket.com/albums/hh143/WallyWallWhackr/DVD_Database_pic.jpg

I do eventually want it in access though.

 

[Peter Hibbs]

I'm not entirely sure what you are trying to do but the Excel - Access
Converter Utility at :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

may be able to help you.

Peter Hibbs.

 

[The Last Mimsy]

Did you even look at the photo link?

I'm not entirely sure what you are trying to do but the Excel - Access
Converter Utility at :-

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

may be able to help you.

Peter Hibbs.

 

[Peter Hibbs]

Yes I did but that doesn't tell me very much about the layout of your
spreadsheet. Usually when a lot of data is stored in a spreadsheet and
it needs to be transferred to a database the data has to be
'normalised' so that it can be stored in two (or more) tables in the
database. If that is the case for your project then the converter
program may be able to do it automatically rather than having to do it
manually as I believe Maury was describing. It will depend on how your
data is stored in the spreadsheet and how you want to store it in the
database. You also appear to be storing images in the spreadsheet
which is another issue, I suggest you sort out the text data first
before you tackle that problem.

Peter Hibbs.