Hi All!

I have been working on this project for a while now and thanks to everyone for the code examples that get posted here. I incorporated a bunch of them. I figured I'd show everyone. I'll work on a stripped out copy to post as I have some original code that I can't share.

Right now, I am working on porting the data to SQL Server 2008 to make my IT department happy. I still have to tune the database and change my DAO code over to ADODB. My main routine, which is to schedule a new contract (About 1400 new rows between two tables), takes about 20 seconds to run when the setup was a normal access database. Once I changed it to a FE/BE setup, it takes about 7 minutes to run. Eventually, the plan is to try to create some three way relationship between SharePoint 2010 Access Web Services, a dedicated SQL Server with Analysis Services and the Access FE. I'm waiting on IT to finishing getting the bugs out of the SharePoint 2010 deployment and for Office 2010 to be distributed. One day, I plan on converting the Access FE to C#/F#.

I have kinda lost track of everyone's contributions so if you see something, tell me so I can give credit where credit is due.

System Login Screen. You can change your password. It is based on NTID and also has a Guest account that bypasses the authentication system. If the system sees that this is your first time entering the system, it will prompt you to change your password and select your default contract settings and theme.
Click to view attachment

This came with the Login Form. I haven't decided if I want to keep it yet.
Click to view attachment

Here is the main Workspace. If you unmaximize it, it becomes a border less form that I have made moveable if you click on the title and resizeable if you click on the lower left hand corner. I still have to expand it so you can click any border to resize. It was a pain to find the code to do this, so I think I will post that code to the wiki for everyone.
Click to view attachment

Each user can select their default contracts and such.
Click to view attachment

Here is where each user can manage their data.
Click to view attachment

Looks quite slick - shows off some of the user interface possibilities in Access.

Mike

My only comment (and this is nothing about the design) is that I would guess that a stored Procedure would run your main routine in less than a second... this is just a quess without knowing what it does but I wrote a SP today that retrieves thousands of records from a table with 16 million records in, manipulates them in an in memory table and writes them back and that runs in less than a second... so in theory 1400 records is a blink of an eye

Thanks for the tip. I'll look into it. The main functionality is currently a VBA class but the more I can port to SQL, the less C# I'll have to do later.

Looks great.

Would you mind sharing which code example you used to create the Workspace and sidebar menu?

This is the workspace/sidebar I worked from:

http://www.utteraccess.com/forum/forums.html...st

This is the original theme manager:

http://www.utteraccess.com/forum/ACC2000-c...r-t1604141.html

This is the login/authentication system:

http://www.utteraccess.com/forum/Custom-Lo...cc-t427208.html

I have to really thank Dom DXecutioner since two of three are his.

Thanks for sharing.

Hi

I made a routine to pass between dao & adodb. I'm pretty good with dao but needed to call SQL server. I didn't want to try and alter all of my recordset code so I made a routine that passed from the dao code and allowed me to retrieve data from SQL

I'll post it

Nigel

That would be great Nigel! I'm just using linked tables and DAO right now. I don't really want to have to rewrite all the DAO code to ADODB.

Hi OriginalRead, I have also recently used the SideMenUInterface for my database.

I am self taught in MS Access so a little out of my depth.

When I open my forms in "the workspace" via the sidemenu I would like to specify where the cursor is when my form opens.

Do you do this? If so can you give me some guidance as to what to do?

Thanks

Neil

Neil,

It is best that you start your own question in a new post. Avoiding hijacking a post. Most of the tinme the hijacker's post is ignored.

To answer you question, use the form's On Load or On Current event to set the focus to the desired control. Also be sure to set the tab order.

Very clean look!

I'm glad to someone has been able to put some of my tweeking of the side menu to good use.

Update time! I have been working hard on getting this done. I have to say that it is coming along nicely. I have my Outlook theme on right now.

Click to view attachment

Added icon control to the Groups
Click to view attachment

Added in Active Group Theme Controls
Click to view attachment

I made a custom ribbon for the show/hide nav commands.
Click to view attachment

Sometime in the next week, I'll release the code I am making right now to make custom ribbons on the fly. I think it should be useful for everyone and FREE.

WOW! Looking forward to seeing it.

Nice work. These are two of the features I've been working on. (I actually included a hard coded method for active group in my last posted version)

I'd be interested to see the methods you're using in comparison to what I'm doing.

I'll post when I get a free moment the code I used.

My ribbon code is a bit more complicated then expected. I'm going down a different path with the design then most ribbon editors that I have seen. I am using a table system to reuse controls. I have decided to turn it into a COM adding. I need to learn C# and .net more fluently then I do now so this would be a good project. My experience with .net is mostly in data sets and sharepoint.

Thanks for the update.

Sorry all for the lack of updates. I got promoted at work.

Still working on a ribbon editor but I have also started to develop a Communicator Class for use in Access so users can IM people and see their status right from access.

Congratulations!



Thanks for the update.

Have you looked in the Access Code Archive forum here? There are lots of great examples that might help.

Looking forward to seeing it when you ready to post it.

It's been awhile. We lost a major contract bid so I got moved to a new project which had nothing to do with Access. The programs that were looking for my Access work finally started to ask for it, so I am finally back to work.

I have had to make a few changes which I will post some more details in the next few weeks. Since I am connecting to SQL server, I need to encrypt the connection string per company policy. Instead of dealing with keeping track of a compiled version and having to push an update every time I have to make a password change, I am making a custom C#/F# Com Wrapper that I can reference in Access to do all my data access. Basically, I am making a custom data provider for Access that is secure. ASP.net will hold the encrypted connection strings and the com wrapper will pull the correct string based on the person who is logged in.

Tons more to come. I'll try to post some pictures tomorrow.

Thanks for the update. Looking forward to learning more about what you are doing.


My solution is to use Windows Authentication. This way there are no credentials stored in the Access database.

Is this what you are using?

Securing Connection Strings

Also see:

Power Tip: Improve the security of database connections

I can't use windows authentication since I can't add all the users to the SQL server or this would be so much easier.



This is pretty much what I am using but the company has a custom encryption dll I have to use.

With Windows Authentication you do not add the users to the SQL server. You add them to the Domain's Active Directory. Adding the used to the SQL server would be using SQL Server Authentication not Windows authentication.

Did you read the Power Tip about using a Cached connection?

I'm a bit on a beginner side with Active Directory and permissions. For development, I can just use Windows Authentication and the SQL Server Admins add me to the server as Windows Authentication. For production, they do not do this as they don't want to be user administrators for every application and I can't have permissions to add people myself. So they create an SQL Server Authentication account for the application that we must encrypt to use.

I did read the power tips (thanks! ), but since I need to pass an SQL Server Authentication login, the cached example doesn't apply to get the original SQL connection string. Thou I might be able to use it after I get the connect string, it would make my design much simpler.

Hypothetically, I could pass through command line the SQL Server, Username and password as one concatenated string into a startup macro which calls the cached SQL Function. Add a little code to parse the concatenated string and I could be good to go. I just have to figure out how to reference the connection from my code behinds when I run a function.

The Cached connection should be exactly what you need. Works great for me.

You would store the string in a file that is encrypted using your encryption DLL.

When the database loads you would use VBA code to open the file and decrypt it to get the connection string. Use the string to make a Cached connection.

Woo Hoo!

Thanks a bunch. Your advice set me on the right path. I have to mess with the Cached Connection a bit but it works like a charm now. What I thought was going to take a whole bunch of code actually only took a few lines of C#. Since the encryption file is a complied .dll, I had to make a class around it and make that com accessible so I can reference it in Access. Now I have the choice of using the Cached Connection or just call the new com reference I made to fetch the string when I hit the database. Leaning toward the cached solution so I don't have to be making a bunch of calls back to the server to get the SQL Connection String.

Glad to hear that it was helpful.