Search results for query: *

No array formula or UDF's (volatile or otherwise) in this tool. Actually almost no formulas at all. Just several sheets with source data and about 1500 lines of VBA code to turn that data into way more data. :-) I've reworked the code in the last few days to hold all the calculated data in...

N1GHTEYES: thanks for that... the source link you gave in that thread had some stuff I'd never seen before - great resource! Have a star... not sure if it will solve my issue, but it gives me some new things to try. Should keep my Monday interesting. :) VBAjedi [swords]

I guess I should do a better job articulating what my code is accomplishing big-picture here in case someone can think of a better way. My code iterates through 20,000 rows of data on a sheet in the workbook, creating ten rows of new data from each source row (and adding the ten identifier...

Mintjulep: that idea has promise... for some reason I hadn't thought of being able to pass both arrays as arguments to a function. My only concern would be with potential performance hits... I've got ten arrays, each 20000 long in the dimension I'd be iterating through. So this function would...

THE QUESTION: Given a 1-dimensional array like: RowIdentifiers(1 To 10) As String And multiple two-dimension arrays, each in the form: ArrayA(1 To 50000, 1 To 100) As String Is there a short (1-line?) way to copy the contents of RowIdentifiers into ArrayA(1, 1-10). Something like: ArrayA(1...

Got it. My hunch was right... a tweak I recently made to the underlying query (joining another query to it to pull in one field) somehow broke the add/update ability of that query. Still not sure why, but undoing it cleared up the issue. Now to sort THAT problem out... Thanks! VBAjedi [swords]

That sounded promising, but unfortunately when I checked the query's permissions they were as they should be (giving my login full access) The fact that only one form is acting up makes me suspect that this might not be a User-level security problem after all. Maybe it's an error caused by an...

I built out an Access 2003 database with a variety of forms/subforms, and then today I turned on user-level security. Now one of the subforms is "frozen" - it won't let me add new records or edit existing ones. I CAN go into the underlying table and edit values there directly. As far as I...

Both good thoughts. I came to the same conclusion on doing the left join first - much faster and doesn't generate a subquery result set in the millions of rows. :) Thanks! VBAjedi [swords]

lameid, That didn't work either. Got a "Join expression not supported" error. It occurred to me that I forgot to mention that I'm working in Access 2003. Thinking that maybe this kind of join wasn't supported back then, I tried some workarounds and discovered a two-query approach that appears...

Wow, that looks brilliantly simple! I wasn't expecting a possible solution to this to be that clean/minimalist. That said, I'm getting a "Syntax error in JOIN operation" error. I'm sure it's something simple. Is the missing left parentheses in your example a possible reason for this? I tried...

I have three tables - I'll call them TableA, TableB, and Corrections (the actual names and data would take too long to explain). TableA contains handkeyed data, including a "TableBValue1" field which is a foreign key used to link to TableB. The problem is that the data entry folks consistently...

Well, I tried using a different version of the ODBC driver for my data source. At first it appeared to work (the query I was working on started working correctly), but then the behavior resurfaced when I built a new (similar) query. Then a coworker suggested I turn on the Totals row in Query...

Hey lameid, It's a DB2 database. I'll check into whether there's an issue with the ODBC driver, but it's worked fine for me in the past in querying other tables in that database. In case my original hunch was right and this is related to the lack of a proper primary key, I'm also trying the...

The "Where" segment of the following query is not working. Specifically, while it does return rows that match, the result set also includes rows with totally different values in them ("2005", "2007", etc): SELECT SFMSPUSR_INDEX_NO.APPN_YEAR, SFMSPUSR_INDEX_NO.INDEX_NO...

Yeah, this is government work. We're on Office 2003... the upgrade to Office 2010 is "coming" but it will be next fall before it actually gets here. Maddening! I decided to go ahead and code out the necessary pieces to open an ADOBC connection and write the records across to an Access table one...

Hmmm... that sounds interesting, PH! I'm dealing with more rows than Excel can handle on a single worksheet, but for testing purposes I've broken up the output to multiple sheets. So I do have it on sheets that I could import from Access. However, one of the things I'm after here is to see if...

For now my boss wants it in Excel. The code reads all of its runtime parameters from settings worksheets, and also uses some worksheet functions (VLOOKUP, etc). Porting the code over to Access is definitely feasible, just not in the cards at the moment. So if that's the best option, what's...

Hey all, I've written some VBA in Excel that will generate about 100,000 rows of data, and I need these to end up in a new table in an existing Access database. Performance/speed is obviously a concern here. Conceptually, would it be best to load the rows into an ADO recordset as I'm looping...

Ooohh... a new toy! Never played with FillAcrossSheets before. ;-) Have a star... I've already got a variant on what Skip suggested working, but I'll have to try out FillAcrossSheets. Thanks for the tip! VBAjedi [swords]