Disconnected Data Connection... OLEDB?

As far as I am aware OLEDB is generally used to create a linked table - so the data will be permanently linked. What I think you need to use is an ADODB connection which would be more like the ODO.Net that you are used to.

With that you create a connection and execute your commands.

Kelvin

Thanks for the correction. I found this I am reading now. It seems to be helpful.

http://accessexperts.com/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/

Mike

Hi Kelvin,

Thank you for the good and promising information. Because this is my first project with Access to SQL Server, I could use your couching and learn from you. Could you please send me an email (under "About me" tab in my profile for my email).

Regards,

Mike

Hi Mike

I avoid that type of contact where possible. It's probably not in accordance with the EE rules, and the one time I did that for someone, it did end up a little unsatisfactorily. I'm happy to continue this thread/question in the EE arena.


Kelvin

Kelvin,

Thanks for the response. I will do just that. But if you ever decided to contact me, you know  where my contact information is.

I am preparing some related question to post soon.

Regards,

Mike

Thanks Mike,

If there's any clarification or example you need to the comments above, feel free to add them to this thread.


Kelvin

Thanks for the follow up. What do you think about the plan of action following criteria A and B below:

A - There are about 25 five users in different cities.
B - Users upon log-in need to only focus on their records (100 to 2000 records).

In the absence of disconnected architecture, like ADO.net, I was thinking upon log-in, the user specific data to be transferred to the access front-end with its local tables identical to those in the SQL Server but with little amount of user-specific data. Now user works with the local data and there is no server traffic. At the end of the day (or session, one-in-a-while) user saves the data back to SQL Server.

The update and or insert in SQL Server is supposed to take place one record at a time via stored procedure. There is a check box (chkCommitedYN) for each record with default value of false in the front-end local tables. The true/false return from the stored procedure is to change the value of chkCommitedYN to true if the record is committed to SQL Server table successfully. This boolean field (chkCommitedYN) if left with false value signals that particular record is not successfully committed to SQL Server and requires some corrective actions to take place.

This is how I make things complicated. So I need to stop reinventing wheel but learn about the best practice from somebody you.

So, here is where I am very eager to take some advise possibly some sample code from you to test and then apply to my application.

Regards,

Mike

Noted, I'll think on this for a bit and come back to you.

Kelvin

Thanks.

btw, my previous comment had been modified to better describe the project. If some points weren't clear, you must have read the version before my latest edit.

Mike

Hi Mike

Firstly, disconnected vs connected. With your ADO.Net background - if it were to be disconnected I suspect a browser solution would be easier. Access was built on the premise of connected tables.

If connected - what connection - two real options ODBC or OLEDB. ODBC requires a DSN on every PC that is connecting - often difficult to manage if off-site. My favourite is OLEDB and DSN-Less connections. You can manage the connection via VBA and don't need a DSN or any other file.

I personally don't like the idea of mirroring tables in Access - keeping everything in sync is bound to come back and bite you.

Access by nature is a thick client and typically has every record of a table bound to form. There is a middle ground here, which may help you. I use a combo to display enough data from each record to enable the user to select the record they want. I then bind the form to that record only and allow editing etc through the rules I create on the form. It is relatively light and easy to save across a network (and is also quite light on traffic).

Have a think on this and get back to me with any questions.


Kelvin

Hi Kelvin,

I appreciate for sharing your wealth of knowledge with me. I am a bit familar with ADO.net and it is desired to have the application eventually on the Web.

Becuase I have many years of experience in access environment compared to few years of web development and/or ADO.net. And because this has to be up and running by mid December, I felt less confident to go ADO.net and Web method.

In a less than a month, I have been able to gather the requirement, develope the table strucure (about 14 table), handle the edit and search interfaces (80% done but all in Access). Tomarrow one or two people will be testing the interfaces to give me some intial feedback. This application is a data driven 90% point and click data entry where users can add new terms to a drop box or retire them with a few clicks and no programming needs).

The remaining task is to create (upsize) the tables in SQL Server and apply OLEDB to it.

This work after launch, although a prototype, but it will be used as a production application. Laer, in a few months, it will be replaced with a web version using ADO.net and most likely asp environment using c#.

That is where I am heading to.

Regards,

Mike

The important this is that there are right and wrong answers. It's what works best in the environment. OLEDB is probably the easiest. If networking gets in the way, can it be hosted and accessed via Citrix, RDP (maybe in a virtualised environment) or Terminals Services?

Given the short life span, they may have to accept the less than perfect performance initially.


Kelvin

Thank Kelvin,

So because this project has many issues to be discussed; my original thinking was, to ge in to one-to-one communication with someone. Maybe this time it will be a better experience with compensation. However, I will continue communicating via EE until you decide otherwise.

Regards,

Mike

Happy to do so.

As part of your coaching, there are certain routines (stored procedures of various types, functions, etc.) I need some sample of to try with access front end to see what can I do. Or, put together an spec (description of what is needed) for your comment and possible assistance.

After I get an feedback from you, I will do accordingly.

Thanks,

Mike

Generally speaking, no. The stored procedures are part of the business rules of your database. All Access does is to identify any input parameters and pass them to the stored proc for execution. I do have std scripts for executing stored procs (with and without parameters). I usually create a function for each of these and call them as needed.

One of the early things you'll need to decide is what type of database authentication you will use. If windows authentication, will that work over your distributed network, and will all users have the same grants - maybe using AD groups if there are separate sets of grants. If SQL Server authentication, you need to think through how security will work.

I will find out about user authentication to match the existing authentication mehods. I wonder if I could send you the quick start document I am putting together for you to see what we are dealing with? Your existing routines could possibly, with some minor twicks, be what I need.

Thanks,

Mike

No problem

Mike,

You appear to work a similar timezone to me - where are you located? I don't usually experience that.

Los Angeles.

Nelson, New Zealand - I think you're about 21 hours behind NZ at present.

Or we are 3 hours apart. Sometimes I work late at nights. So, I am preparing some information to email it to you. There will be lots of specific questions; that I will continue posting them at EE. I am a member since 2002 and really like this site.

Mike

I generally work 6.30am to about 9 or 10 pm Monday to Thursday (Sunday to Wednesday your time) and about 6.30 to 4 on a Friday. Have been on EE a similar since about 2005. Have been Access dev since mid 1990's and Access over SQL Server since 2002. Mainly working scripting SQL Server and Oracle scripts these days, with some additional Access dev for good measure.

I find EE one of the best resources - don't chase questions that hard as rank is not really a driver for me. If I appear to have missed a question you post - feel free to add a chaser comment to catch my eye.

Kelvin

There are some similarities in our background except you are more knowledgeable. I also started my database work in 1990's with Access 2.0. I will send my resume to better describe my background and experience. Do you do Web developments as well?

Mike

NO, I have never ventured into web development - rather took my experience into large databases (still only beginner to intermediate with Oracle).

Also started on Access 2.0 when I outgrew Lotus 123 (back in the DOS days)

Kelvin

Do you want to have my resume to see what kind of help I may need?

Mike

Don't think so. Its just a matter of walking forward and addressing the issues as they arise in the most pragmatic way.

Thanks