Search results

Hi, I have an Access database that has been working normally for years. No recent changes on my end, although my corporate IT environment likes to change stuff silently in the background. It was built in Access 2016 and currently running on O365 32-bit v2202 b16.0.14931.20602. It has very...

Hi all, Having a bit of a brain fart. I have a file that I import into a table in Access 2016. The data ends up like this: MainTableID PicURLs 1 www.example.com;www.abc.com;www.xyz.com 2 3 www.access-programmers.co.uk The field PicURLs are zero or...

Hi all, I have a piece of code in one of my databases that runs 22 queries to import, delete, and append external data to the local tables. This process--due to one or two large queries in the source database--takes around 2-3 minutes, which is not a problem in itself. However, I have noticed...

Hi all, I am designing a new database and trying to do it properly. Just getting my head around table relationships... are ambiguous joins redundant? Do I need to create a relationship in every table wherever a foreign key appears with the parent's parent table primary key? i.e. in the image...

I have just finished rebuilding my join functions to implement recursive behaviour (where the function calls itself), an idea taken from my recent build of a function that joins Excel ranges together. These functions mimic the behaviour of the Join() function which joins an array of strings...

Hi, I have a corporate email address, e.g. stormin@work.com, of which I want to check if emails are getting through to. Some incoming attachments cause external emails to fail to be delivered to this address. They are caught at the company's firewall and the sender gets a Delivery Fail message...

Hi all, When importing files to my Access tables I keep track of how many records have been added / removed at each stage by counting the number of records at different points and then finding the differences where I'm interested. When I designed my import function I used...

I have a db that takes a lot of 'dirty' imports from different systems. I have a lot of queries and reference tables that comb through the data, clean it up, fill in missing data, run calculations, and transform it into a unified format. Some of the 'final' select queries run quite slow. For...

Hi all, So I've wondered this for a while but cannot find any answers on Google due to the keywords being too general. With a For...Next loop I've always specified my variable after the "Next" statement e.g. For i = 1 to 5 Debug.Print i Next i or For Each i In myArray Debug.Print i...

Hi all, I recently wanted to implement use of the status bar and/or progress bar (bottom left and bottom right in Access window respectively) in some VBA code to give the end user some info about what is happening behind the hourglass. I found how to do it online somewhere (sorry can't remember...

Hi all, I recently wanted to implement use of the status bar and/or progress bar (bottom left and bottom right in Access window respectively) in some VBA code to give the end user some info about what is happening behind the hourglass. I found how to do it online somewhere (sorry can't remember...

Hello, I have an auto-generated report in .xls format that I import daily into my 2010 database. In some rare circumstances there is data missing from fields (empty cells) which, when imported, are Zero Length Strings in Access. I have everything in my database set to disallow ZLS since I use...

Hi all, I am trying to call a procedure with paramters from within another procedure in Access 2010. I've used this method before but for some reason it's throwing a strange error this time. For testing I have created a new normal module, Module 1, and put in the following code: Option...

Hi, Background: I have created a table that is going to hold daily stock snapshots used for reporting. The data is 'in flow' and so there are constant incoming and outgoing items from stock, and constant status changes of those stock items for different stages within the process. e.g. Today's...

Hi all, I am looking to categorise the records in my database based on two fields, say [Rec_Type] and [Rec_Age]. I have my categories in a table like this: [Rec_Type] [Age_Min] [Age_Max] [Cat_Desc] TypeFoo 0 12 TypeFoo 0-12 months TypeFoo 13 24 TypeFoo 13-24 months TypeMan 0 4 TypeMan...

I have created a Ribbon button to switch the "MaintainConnection" property of the (first) pivot table's OBDC on the active worksheet. This throws an error when the pivot table's source is local, I assume because the property doesn't exist. Currently I have the code set to exit when any error...

Hello, I am trying to connect an Excel 2010 workbook to an Access 2010 database (2007 type) query to use as a PivotTable source, using Data > Connections > Add... . I can establish a connection to the db itself but I noticed that a few queries do not show up in the table/query selector wizard...

I have some VBA code that (essentially) takes a user input for a date, and uses that date in an update query SQL. However the problem I'm having is that my date is in local UK format, but when SQL runs it converts it to US format e.g. 5th Jan 2017 is inputted and stored as 05/01/2017 as Date...

Hi all, I have a working database that I built. Recently (on a copy) I added some more functionality to a table that stores vehicle model codes i.e. factory code that designates model, engine, gearbox, trim level etc. The long model code that is stored in the table is a concatenation of...

So I have a feeling I'm probably over complicating this but... I have some code that imports a certain Worksheet from an Excel file into an Access table. This Excel file is generated from a preset report in another department, and they can potentially change the report preset so that the...