Solved

Disconnected Data Connection... OLEDB?

Posted on 2013-11-17
29
390 Views
Last Modified: 2013-11-27
I am somewhat familiar with ADO.net, but now I am trying to do a project in vba (Access 2007) with SQL Server 2000 backend in a disconnected manner.

Question: Does OLEDB has a similar architecture as ADO.net in this regard?

Basically I am looking for comparison and to make sure OLEDB is the way to go in my project. Any comparison, link, or place to start will be appreciated.

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 15
  • 14
29 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39655107
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39655461
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
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
ID: 39655478
You can also create commands and execute stored procedures using ADODB. One technique I use often is to code insert/update and delete statements in VBA and use ADODB to pass them to SQL Server for execution. The processing is then happening at the server, rather than within Access.

Kelvin
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39672879
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39672976
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
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 39673027
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39673164
Thanks Mike,

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


Kelvin
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39673486
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39673550
Noted, I'll think on this for a bit and come back to you.

Kelvin
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39673723
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39673776
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39676094
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39676195
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39676230
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
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39676238
Happy to do so.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39679873
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39679897
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.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39681719
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39681730
No problem
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39681737
Mike,

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

Author Comment

by:Mike Eghtebas
ID: 39681786
Los Angeles.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39681820
Nelson, New Zealand - I think you're about 21 hours behind NZ at present.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39681981
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39682020
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39682065
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39682129
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
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39682300
Do you want to have my resume to see what kind of help I may need?

Mike
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39682348
Don't think so. Its just a matter of walking forward and addressing the issues as they arise in the most pragmatic way.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 39682421
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now