Search results

I have a form with a button on it that creates a linked table, runs a query to append the linked table data to a local temp table, then deletes the linked table. I'm using Access to modify an Excel file and export right back to Excel. The process isn't related to the rest of my database. I...

I have one saved query object that uses the deleted linked table. So far I'm not having any problems with C&R breaking the query or breaking anything else. I'm using the code I posted in post #21 above. The linked Excel file is only used in an append query and then the linked table object is...

I decided to test this, out of curiosity. I can only duplicate the "bug" in two scenarios: 1) attempting to open the linked table in design view when the linked file is missing, and 2) opening any form that has a subform, making design changes to the form, then saving. First scenario is...

The demo looks like it would be useful for that particular scenario where normalization is needed. In my case, my module to import from Excel is separate from the rest of my database and it's purpose is only to modify an Excel file and then export a new version of it back to Excel. I don't...

If I leave the linked table saved in Access then I'll be back to my problem of Access creating blank Excel workbooks where the linked file used to be (that seems like a bug to me). The Excel files that I import come from a network folder and someone eventually moves those files. I could copy...

Thanks for the reply and I hope you are doing well today. I am trying to think of why I decided to save a linked table object and use .Connect and .RefreshLink to change the source instead of using TransferSpreadsheet. But I can't remember the reason. It could be that I was seeing different...

Can you explain a little more on how you link and append? Are you deleting the linked table after running the append query? I have been doing this by changing the source of a linked table, then appending from that linked table to a local table. The problem with doing it this way is that once...

When building an SQL string, form controls must be concatenated as strings but functions do not. Why? In the examples below, textbox contains text and MyFunction returns a string. Example with form control: db.Execute "UPDATE table SET field = 'some text" & Forms!MyForm!textbox & "';"...

I wonder if I had that feature all last year and didn't notice until yesterday. I've noticed some cosmetic changes over the last year with updates but I don't think I had the SQL view feature. Maybe I completely overlooked it for months. Either way, it's great that it's finally implemented.

I just noticed with a recent update [Microsoft® Access® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20336) 64-bit] that I can FINALLY open a saved query in SQL view by right clicking the object in the navigation pane. The absence of this feature has puzzled and plagued me for several...

Thanks, I'll write the full code later. I just wanted to provide the SQL for now to get feedback on my plan. Ideally I won't need to make combo box selections for each query that I run. My example query is just one of several, and I don't want to think about which combo box selections to make...

I'm back after being disabled by the CrowdStrike incident... and I think I may be getting close to an answer to my original question. The first part of the answer is: don't hard code IDs in queries. The second part of the answer is: if you are trying to run UPDATE queries to change foreign...

Yes, I will fix the tables. I agree that it needs to be done. I understand the StackOverflow question but not for the scenario given in the question. Maybe I understand the question because my ideas about table design are wrong. It wouldn't make sense to assign equipment to users with an...

Attached is a screenshot of my Relationships window. One main table with six lookup tables. No parent/child relationships. Until recently, I had only one dollars field and one positions field, and an additional field to identify the fiscal year for dollars/positions. I removed the fiscal...

I see that happen when the list of values is too long. I think you can increase the number allowed in Access options.

Yes, pasting in continuous view or datasheet view works correctly as long as the number of columns is the same and field types are the same. The data is stored in the table that the form is bound to exactly as it should. Now that I know datasheet column names (and other things) can be...

That's what is stated above but I haven't tried it. I think I'd rather specify names per form anyway. I wish I saw the Datasheet Caption property a long time ago. You can have short, non spaced table field names but long, spaced datasheet names in the form. Customizing the appearance is the...

I never noticed that form controls have a Datasheet Caption property to display whatever you want in the datasheet column heading. That's huge. Column names might be the biggest reason for me using continuous forms instead of datasheets. Now that I know how to customize datasheet column names...

If I paste multiple rows from Excel into a continuous form, and the first row copied from Excel does not contain the matching field names in the form, I receive the following prompt: "None of the field names you pasted onto the clipboard match the field names on the form. Access treats the data...

What is the difference between the three Set lines below? Dim db As DAO.Database Set db = OpenDatabase("C:\Northwind.accdb") Set db = DBEngine(0).OpenDatabase("C:\Northwind.accdb") Set db = DBEngine.WorkSpaces(0).OpenDatabase("C:\Northwind.accdb") And should either/both of these lines be at...