Can't get Query updatable

[davesmith202]

My head is exploding trying to work out how to get a query to update. It is based on a many to many relationship, from a database that someone else designed. So already, it hurts!

Is it possible to upload the database here, so someone could take a peek and tell me why I can't add a record?

Thanks,

Dave

 

[boblarson]

1. Yes, you can upload here (remember to do a compact and repair first and then zip the file so the final file size is under 765 Kb).

2. Also, you might look at this to see if you can spot any reason why your query might not be updateable.

 

[davesmith202]

Is there any way to easily scramble the data in there so that it is not exposed? :S

 

[boblarson]

Try this out (for scrambling data).

 

[davesmith202]

The database is Access 2007. Would I export the tables to 2000 format, scramble and re-import?

 

[boblarson]

The database is Access 2007. Would I export the tables to 2000 format, scramble and re-import?

You should be able to just import the scrambler form (not the tables) into your COPY of your database and then run it. The tables in the sample are just samples to work with.

 

[davesmith202]

It doesn't seem to work in Access 2007 and the site says its for Access 2000.

 

[boblarson]

It doesn't seem to work in Access 2007 and the site says its for Access 2000.

It does work in 2007 but I just realized that it isn't really working the way we THINK it should be working. It doesn't scramble up the names it just moves the records all around so it doesn't have the exact data together. I'll look for another one as I didn't realize it wasn't actually scrambling the names, etc.

 

[davesmith202]

Or I could just do a find and replace on the tables...

 

[boblarson]

Or I could just do a find and replace on the tables...

Try this one. I modified it to actually scramble the data.

It is in 2003 format but you should be able to import into a 2007 database and then just make sure that DAO is checked in the references (either DAO or the ACE engine reference).

 

[boblarson]

Just realized that the combo to select the tables is not set properly and will only grab tables where it starts with a tbl prefix. You can modify that, or just do whatever you need to. I know this exercise has turned into a pain in the ....

 

[davesmith202]

Thanks Bob. I notice on the Field Name combo, it comes up with an error message so I think there is something that needs updating on there too.

 

[boblarson]

Thanks Bob. I notice on the Field Name combo, it comes up with an error message so I think there is something that needs updating on there too.

What error message? I'm not getting one so if you can give the text of it, I might have an idea on why it is doing that.

 

[davesmith202]

"The record source '~sq_cDataScramble~sq_ccboFieldName1' specified on this form or report does not exist."

It appears when I first click the drop down on the Field Name combo. Maybe something to do with the fact I removed tbl from the Table Name filter and so its showing other Objects?

 

[boblarson]

"The record source '~sq_cDataScramble~sq_ccboFieldName1' specified on this form or report does not exist."

It appears when I first click the drop down on the Field Name combo. Maybe something to do with the fact I removed tbl from the Table Name filter and so its showing other Objects?

Might be. Well, I can try to modify the form again to see if we can get the tables but without all of the system stuff, and make sure that some error handling is in place. Up to you whether you want to go that route or just manually mess up the data.

 

[davesmith202]

I don't mind actually. Might be easier for me to just do a find and replace. On the other hand, it might be useful for these forums to have such a tool.

 

[boblarson]

Will have to work on it tomorrow. Gotta run now to go home and get to a concert.

 

[davesmith202]

Ok Bob, whenever you are ready.

 

[boblarson]

See if this one works for you.

 

[davesmith202]

It broke here:

'*** delete the table if it exists
CurrentDb.TableDefs.Delete "tblRandom"