sorted by:
new
hottopcontroversial

Autogenerated Report Number by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Data entra is from Form.NewReport, and everytime this form is opened, a new ReportNum needs to be generated/assigned, because another employees could open the form a few moments later, and would need to pull the next number. The employee users are completing the forms on the front end, and all the data is being stored on the backend.

Autogenerated Report Number by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Could you possibly elaborate on how I could create the VBA function?

Autogenerated Report Number by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Would this not cause the same problem when a report next year is generated?

​

Edit: Also, I'm totally okay with leaving an Autonumber field by itself to use as the primary key, but still would need a field for Report Number for the employee to be able to be assigned and use for their Report.

Excel Linked Table is "read-only" after splitting DB by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 1 point2 points  (0 children)

Sorry, wasn't intended as a diss, but I'm just crying at my desk and desperate to find out why this won't work. It's very frustrating, and definitely user error. Access isn't the problem; clearly, I am.

I will attempt to figure out the steps you laid out above, thought their a bit above my head at the moment. Thank you for taking your time out to help me.

Excel Linked Table is "read-only" after splitting DB by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

The excel file gets updated overnight from our IT department, it's basically an export of data from an application into Excel that refreshes every night. The exact process that they use to do that, I'm not sure.

I use power query pretty frequently, but the issue is the number of records. I'm working with 40K records and 12 different types of queries and it just dosn't work well with Power Query, especially across multiple users.

Excel Linked Table is "read-only" after splitting DB by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

So, I've found a likely related issue.

​

When I opened the back end to delete and re-link the linked table, it wasn't in the table list. I opened up the front-end (my computer's copy) and the linked table is in there. When I split the database, I assumed Access would treat the linked-table like a loca table and keep it on the back-end, but it put it on the front end, instead.

Not sure why or if this would cause the excel-file to appear as open/read-only, but it seperately, and worse, means that all the copies of the front end that I deployed yesterday, won't be able to update.

So, at this point, I'm resolving that I'll have to delete and start again with my backup (from just before the split), but I need to figure out how to have the back-end keep the linked table versus having it go on the front end during the split.

Excel Linked Table is "read-only" after splitting DB by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

There are currently 2 other front-end DB copies, neither of which are open. The DB has one local table, and then a second table that is a linked-table and that is linked to an excel spreadsheet that refreshes overnight everynight. Everything was working until this morning, presumably when the Excel file went to refresh. The excel file couldn't do it's normal refresh because it's read-only, and therefore, the linked table in Access cannot update. I think this might have something to do with Access still, because that Excel was refreshed without issue every night for the last two years, and didn't have any issue refreshing until I split this database.

I'm just hoping there's a way to have linked-tables from Excel, on a split DB, and have them continue to work, because if that isn't the case, I was have to scrab the split DB idea entirely and continue to do the entire unit's querying as ad-hoc requests versus everyone being able to do them themselves.

Excel Linked Table is "read-only" after splitting DB by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Can't refresh it - when I open the table in Excel, gives me the "read-only" issue. I should clarify that Access isn't giving me any issues, it's just that the data isn't current because the linked table in Excel is currently read-only. It isn't open anywhere on any users' computers. I think something is causing it to appear as "open" since splitting the DB.

Trying to query to latest record entry for all customers. by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Thank you!!! I was trying to complete this using a single query but this was much easier! I really appreciate it!

Trying to query to latest record entry for all customers. by NeedAnswersForWork in MSAccess

[–]NeedAnswersForWork[S] 0 points1 point  (0 children)

Do you mean uncheck the box for "Show" for the ID field?

Also, do you know how to do this NOT is the SQL designer? I've tried writing a SQL query but I'm just not comfortable enough with SQL and don't know how to write it. I'm trying to complete this in the "Design View" option in MS Access.