MS Access Solutions Houston TX

Hi, all.

The company I work for has an old Access database that I’d like to move the back end to Azure SQL Server.

I think the best way to do this is to migrate all the data to the new Azure db, and then link the tables in the back end file to their azure counterparts (although, if anyone has any suggestions otherwise, they’re welcome!). I have already migrated all of the data to the new azure db.

Setup details:

• The Access db has been split into a back end and ~10 different front ends (for different departments/roles)
• The front-ends will be stored on a mapped network drive (to a network share)
• Approximately 25 people will be using the db from their Active Directory joined computers
• We do have an Azure AD, it is NOT federated (so, no AD FS), but we do have Seamless SingleSSO enabled

The problem lies in the fact that I don’t want users to have to enter a password every time, and I’d rather not store credentials/passwords in not-secure ways. Interestingly enough, on one of the documentation pages from MS about linking Access to on-prem/Azure SQL Server, they specifically note:

If you use SQL Server Authentication, your password is stored in clear text on the linked Access tables. We recommend using Windows Authentication.

… despite the fact that Windows Authentication cannot be used with Azure SQL Databases! (At least, not that I know of). To some extent the easiest and most secure thing would be to use the “Active Directory Password” authentication method, and just have users enter their passwords every time. However, the application requires short but frequent use. So, unless we just started having users leave Access open the whole time, they’d need to enter their passwords very frequently. (Or maybe there’s another way I don’t know of to leave a connection open for a while?)

I’ve looked a bit into using an access token, and I think that’s the most promising avenue for the moment, but I don’t know how well it will work. I’m wondering if SSO enabled would allow the connection WITHOUT any additional authentication, but I don’t know if the ODBC driver (or any provider for that matter) would be able to make use of that.

I’ve also had some out-of-the-box ideas like just using SQL authentication with the password stored in a file on the network drive, but automating a task to run every night to change the password and update the file.

Have other people solved similar problems?

Am I being paranoid?

Any suggestions/advice/direction pointing is welcomed.

Thanks!

P.S. I apologize if it would have been to post to /r/AZURE. I think I will post something there next, but I thought I’d post here first!

Good afternoon,

I am looking for a way to keep track of contact with individuals in my access database. I would like to make a datasheet that I put into a tab in my form. Does anyone know how I can do this? Thank you!

HI,

I have two tables, drivers and trips. I want to show which drivers are not used on a specific day on trips (based on a text box field on a form). I have a trip date field on trips to use for the comparison.

I don’t think i can do this with an unmatched query, and having trouble finding SQL i can use for this.

Any help is appreciated.

The Problem

My switchboard has multiple buttons to open forms. Every time I switch forms, I have to search for the record I was JUST working on. Example: on Form1, I’ve navigated to Record446. When I go to Form2, the default Record1 shows. and I have to go find Record446, manually….again.

The Desired Solution

Wouldn’t it be great to have Form2 load Record446 (or Record50, Record 23, etc.). Especially because I’m switching forms so much.

What I’ve Found Out

I found the “On Deactivate” Event for the forms (Form1), and wouldn’t I put a macro in there to copy the information? I’m not sure what or where or how to copy the field data.

Then, I would have data when “On Activate” event occurs for Form2, where I run “Find Record” the same as the copied record.

Or maybe there’s an easier, automatic way to do this?

Hi there! I am trying to set up a production tracking system for a variety of products whose ingredients and quantities are stored in a BoM table. I have a Query that pulls together all the relevant fields from Products table, Ingredients table, etc and I have a split form that filters the relevant ingredients based on product selection. Now I am trying to set up user input so that they record the product type, the quantity of each of the products’ ingredients, and the lot# of the ingredient: these will all be stored under a productionID in a production table.

My issue is setting up user input that is dependent on the split form entries (one for each ingredient in the selected product). Any help or advice would be very appreciated. Much Love.

Hey guys, I’m certain this is something simple, and my brains just not quite turned on this morning. I have a switchboard that is adding a large white space underneath the 3 options that I currently have. I would like this white space to not be there.

Here is what it looks like: https://imgur.com/a/BX5dVb8

Here are my format settings: https://imgur.com/a/RQkjTe2

Hi, all.

The company I work for has an old Access database that I’d like to move the back end to Azure SQL Server.

I think the best way to do this is to migrate all the data to the new Azure db, and then link the tables in the back end file to their azure counterparts (although, if anyone has any suggestions otherwise, they’re welcome!). I have already migrated all of the data to the new azure db.

Setup details:

• The Access db has been split into a back end and ~10 different front ends (for different departments/roles)
• The front-ends will be stored on a mapped network drive (to a network share)
• Approximately 25 people will be using the db from their Active Directory joined computers
• We do have an Azure AD, it is NOT federated (so, no AD FS), but we do have Seamless SingleSSO enabled

The problem lies in the fact that I don’t want users to have to enter a password every time, and I’d rather not store credentials/passwords in not-secure ways. Interestingly enough, on one of the documentation pages from MS about linking Access to on-prem/Azure SQL Server, they specifically note:

If you use SQL Server Authentication, your password is stored in clear text on the linked Access tables. We recommend using Windows Authentication.

… despite the fact that Windows Authentication cannot be used with Azure SQL Databases! (At least, not that I know of). To some extent the easiest and most secure thing would be to use the “Active Directory Password” authentication method, and just have users enter their passwords every time. However, the application requires short but frequent use. So, unless we just started having users leave Access open the whole time, they’d need to enter their passwords very frequently. (Or maybe there’s another way I don’t know of to leave a connection open for a while?)

I’ve looked a bit into using an access token, and I think that’s the most promising avenue for the moment, but I don’t know how well it will work. I’m wondering if SSO enabled would allow the connection WITHOUT any additional authentication, but I don’t know if the ODBC driver (or any provider for that matter) would be able to make use of that.

I’ve also had some out-of-the-box ideas like just using SQL authentication with the password stored in a file on the network drive, but automating a task to run every night to change the password and update the file.

Have other people solved similar problems?

Am I being paranoid?

Any suggestions/advice/direction pointing is welcomed.

Thanks!

P.S. I apologize if it would have been to post to /r/AZURE. I think I will post something there next, but I thought I’d post here first!

just a background. im thinking of using access to build a gui for an inventory system i had as an idea. i have background on sql and plsql and also a little bit of programming.

1.) i have 2 db installed in my system mysql and postgresql which of the 2 would be most compatible and recommended for access
2) do you need to code in order to link to another table while entering data? i think itll be easier to understand with an example below
ex.
sales form
sales order number:
customer
first name:
last name:
contact:
ordered date:
amount:
for this i have 2 table sales and customer. a. is it possible to pick customer data on the sales order form? do i need to code or just drag and drop and change an option?
b. i want the customer first and last name to be linked to the customer table so if i change anything in the customer table it will reflect on the sales order form.
c. is it possible to also auto fill the contact if i selected a customer?
3.) is it possible to make access not obvious that it was made by access or do i really need to open access?

Forgive my ignorance, but I have limited knowledge of Access. I do some data compiling for my work every quarter using Access and Excel and I have step by step instructions. I have to enter in a lot of codes into the design view of access and it is very time consuming. Is there anyway I can have it all typed up one time and then copy and paste it into access? I attached a photo below to hopefully better explain what I am asking.

https://imgur.com/mAbAf0i