Search results

Hi Pat That's what I was asking about originally - I didn't understand why it wouldn't work. I'm now back to using recordsets as a couple of people have suggested.

I think the calculation I have which compares longitude/latitude is pretty fast for Access to run, so I don't think we could speed it up by using Google Maps. If I needed a 'by road' distance instead of a 'as the crow flies' distance then I'd need some sort of mapping calculation, but...

Hi There's a lot of sense there - I guess I have been using a bit of a 'dumb' way of going about this, and if I could treate adjacent postcodes (as we call them in the UK!) differently to ones that are further out, I could indeed cut down on the number of calculations needed. There's no point...

Hi MajP Thanks for your post. After a compact of the back-end and increasing dbMaxLocksPerFile the code has managed 1,000,000 records in 1 hour and 34 minutes, which I think is pretty good. Unfortunately I've got sidetracked with soemthing urgent, so can't spend much more time on this for now...

Doc_Man: I agree with your reasoning - thanks for the level of detail you went into - but I do need to create the list. The client wants to find the nearest x thousand address records to each of 3000 restaurants, so I will need to be able to produce this. They may change the x as they feel like...

Well, it is slowing down - last few lines it reported have been down to 535,000 per hour, but still a big improvement. I haven't added the bit Auntiejack56 suggested about deciding when the lat was no longer worth checking (too far away), so that should help. The array is just for the...

Hi I just did some initial timings and it looks like I can now get between 600 and 625,000 records per hour - previously it was running at about 100,000. I'll try to do some tidying up and then post the code. Arrays for the win! Many thanks for your input with this - really appreciate it.

Oh I see! The distance is as the crow flies - it's just the difference between longitude and latitude. Anything else would require more calculations than I want to get into!

Will do! Loving the name of your function by the way!

Hi guys Thanks everyone for responses - didn't expect any replies so soon! arnelgp - I used recordsets originally, but I found in the past that queries can be quicker for some stuff, so that's what led me to this route. I may have to use a combination of both. Auntiejack56 - I hadn't thought...

Hi I've not tried to do this before and it's not currently working, so may be not possible, but here's what I'm trying to achieve: I've got 2 tables - an address list and a restaurant list. I've got longitude and latitude values for all records, and I'm trying to flag each address with it's...

Thanks for the replies guys. Doc_Man: The table that Word uses isn't one the form uses. The Access form prepares the data is various ways - adding fields, indexes etc, then opens Word to allow merging from there. Pat: Sounds like you are using methods beyond what I've done before, I'd need to...

OK thanks. I'm doing it this way because a client supplied the word doc and likes to change it periodically, so it seemed to make more sense to leave it as word rather than make it into an Access report.

Hi Yes, the word doc's mail merge source is one of the tables.

Hi I have an Access database (which only I use) with a table. I have a Word doc which uses this table as it's data source. I launch the Word doc with via VBA from Access and it works well unless I've been tinkering with the form designs in Access, in which case I get the message: Error has...

Thanks for reply - great to know you can get help on these forums!

No worries - found it: DoCmd.RunCommand acCmdExportExcel

Hi Can the Export Wizard be called from a VBA module? (I imagine it could be done by sendkeys, but I don't want to go there.....) It would be great if I could pass it the name of a query I want to export as Excel, and then have the wizard take over from there. Thanks for reading.

Sorry for late reply on this guys - been on hols! Unfortunately I'm supplied various documents by clients who like to use Word, so re-creating each one in Access is not feasible. Thanks for the link - it's frustrating to have to export to excel or a text file just to be able to link the data...

Hi I've been having an annoying issue lately when I link to an Access table from a Word mail merge document when the Access database is open and in 'design mode' (ie I may have edited a form recently). I only seem to get this when the Access file is open on a Windows 10 PC (I usually use a...