Any interest in a MS-Access editor for Pick?

I'm just wondering if everybody here is interested in me building a set of
wizards that would connect to a Pick database from MS-Access ?

I mean there is always been ODBC, but the problem is for most people setting
up the SQL mapping commands takes quite a bit of skill on the end users
part. So, I thinking ADO + the d3 object library, or even perhaps the later
.net one.

A wizard type setup with a few forms that lets users browse + pulls up the
files and then dictionaries on a pick system is the idea here. And, I think
something that would allow dropping of fields into a form, and also ensuring
that any multi value field is placed on a form results in a proper repeating
sub form would certainly create a really nice screen editor. And, it also
means people would not have to learn + setup something like Visual Studio.

It seems quite well these days that most already have some decent easy to
use screen editor for their existing data files and applications anyway, so
there is little need here?

Anyway, the reason why I'm thinking aloud here, is tonight I was writing
some disconnected ADO code that pulls some XML from a file into a ADO
reocrdset that then can be edited in a access for. So this got me thinking,
and I said instead of pulling in some xml data, this setup would also work
quite well with something to map out a standard pick multi value record into
ms-Access form.

Is there a need for easy to setup screen editor these days? Or is everyone
just jumping right on into building web based interfaces for editing data
these days, and anything else they have is already useful for the desktop?

Or, is anyone even bothering with the desktop these days?
(MS-Access 2010 does have web creating ability, but it can't be used with
something like a pick data source).

Is there any market or use for the above type of utility for ms-access?

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
email@anonym

"Roger" <email@anonym; wrote in message
news:email@anonym...


can you expand on why access2010 web forms can't be used with a pick
datasource ?

is that because the datasource needs to live on the sharepoint server ?

Yes, the above is the exact reason. (good call on your part)

Remember, SharePoint has what is called BDC.

LATE in the Access 2010 development cycle is they cut out BDC support.

For anyone that don't know, BDC = Business Data Connectivity.

Think of BDC is kind of a super duper odbc for SharePoint data on the web.
Virtually anything that you can make look like a table, be it data from sql
server, SAP, MySql, oracle, heck even stuff from a word document or a
spreadsheet, or even from a web site like Amazon. If you write code to pull
data from ANYTHING and make it look like a table, you are in. Needless to
say, connections to SQL server, or all your contacts in Outlook (exchange)
is provided now and the choices are growing by the minute. So, editing data
in SharePoint really might be you editing your outlook contacts.

So, if you can write some code to take something and make it look like a
table, then you are home free. And, these BDC sources can be updatable. Once
that BDC is setup, then as long as the data can be presented as a table,
then that data can be used as a list in SharePoint. And lists are what are
used for Access web forms.

Here is a video of BDC in SharePoint:
http://www.youtube.com/watch?vnbJbNHHZn8

In the above, they are proving (pitching) a 3rd party provider..but, it fine
for this discussion.

BDC would really been great, as then you could have used Access to report
and build forms to edit near any corporate data that can be grabbed by
SharePoint.

Unfortunately BDC support did not make it into this release (it was in the
beta testing). Note that you CAN hack in with your own .net code to pull
data from other sources when using Access Web Services (but, you be using
events attached to the web services). In fact a developer just did a demo of
doing that while I was in Redmond last month (was pulling data from a 2
million row table in MySql into the access web form).

However, this type of hack is much like tweaking the output code when using
a Pick 4GL system say like ScreenGen or SystemBuilder in Pick. Once you
tweak or touch the output code, then you can't really go back to the source
screen editor + developer system and make changes and then re-gen your code
(since you tweaks will be overwritten next time you re-build the project).
So, like any 4GL system, you don't want to touch the output code directly.

Keep in mind that Access web forms results in AJAX for the browser side
(even the Access code you write gets converted into JavaScript and runs
in/on the desktop browser). The forms output are XAML (zammel) forms (they
can be opened by .net development tools)

In a nutshell for all practical purposes, we can't use the BDC connector and
that feature was CUT. If BDC support was not cut then one could have used
Access to create WEB forms for a Pick data source.

And, just in case anyone not heard, Access 2010 allows one to create web
based (browser) applications. Here is a video of mine where I run an access
application, but at the 1/2 point I switch to running the application 100%
in a browser.

http://www.youtube.com/watch?vU4mH0jPntI

And, a similar but far less polished of the above video where I won an xbox
360 for that demo is here:

http://blogs.msdn.com/access/archive/2010/03/29/access-developer-contest-results.aspx

What is neat about the above is that you get to keep the simple easy to use
bound forms development model that always made access so easy to work with.
Building these access forms for the web is not any harder then building for
the desktop. I think the whole web development market been missing a easy to
use RAD tool for the web like access. I also like that one don't have to
adopt a huge development tool set or framework. You just need the simple
desktop program called Access and away you go. For the server side, you need
SharePoint 2010.

I suppose a demo of using Access to create web forms for pick would have
been really cool, but, with DBC support dropped, then I guess have to wait
until V2 of Access web services to do that demo.

And:

My apologies for using the term "Access" here in places of ms-access. I did
mean ms-access and not the rightful term pick "Access" that most here love
and use all the time. While I don't post here much, I still well know what
Access really means to the people in this group...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKemail@anonym

"Paddy" <email@anonym; wrote in message
news:73a9045c-cb32-4891-855b-

I'm John Racine. I've worked with Pick/Multi-Value for many years and
I can see a lot of value in being able to do MS-ACCESS with Pick. I
tried to put a friend of mine up to writing an API which would provide
TCL query capabilities within MS-ACCESS (instead of SQL).

Keep in mind if you've done the SQL mapping on the Pick side, then if you
fire up the Access query builder now, you can type in directly any TCL
Command such as

LIST Customers LastName FirstName

If and when you run the query inside of Access, it'll just be displayed as
if you typed in SQL.

So, the above works now.

As mentioned, the only issue with the above is that if you have to go into
the Pick side, and set up the mv mapping to SQL. I was thinking of utility
that would eliminate the need to do all of that hoop jumping. It has to just
work. Excel is great, but people can JUST start using it.

I'm not played with the other versions of Mv databases, but when working
with d3 and the ODBC driver, if you map things out then you can be on your
desktop, launch ms-access, and in the query builder you can simply type in
any tcl command direct, hit the ! to run and the results come back as a
table. So there is some great integration tools right now and they are zero
cost. Like everything else the easier you make things and reduce the pain,
the more people tend to use something. At I looking for something to allow
easy GUI browse of your pick databases from access, pick a database and then
work with the tables. This would allow people to quickly and easily edit
data inside of ms-access from pick.

However, for the reporting side of things, that case likely would still if
force people to have to do the SQL mapping trip. However, if could also
eliminate that hassle of having to set up the SQL mapping, especially for
those that don't know SQL, then Access would work well in this case. The
fact of the matter is mv systems were popular with a lot of people and
organizations because some staff person within the organization found the
Pick System easy to learn and easy to use and also easy to do little jobs
and tasks. However, we don't see NEW people doing this. Allowing these
kinds of people to use something like Access would be really nice, because
any issues of reporting, merging data to word, and a whole bunch of other
issues would be solved in a really easy fashion.

However the flip side of the above is, most of these people are quite
independent, and have been using pick for a long time. As a result they
cobbled together quite a bit code and editors and really don't need a lot
more then the great system that they built up over the years.


I can see your method as having some advantages, like being a real-
time update.


Yes.

At the end of the day I think the real issues is the ease + setup of the
connection to the actual database itself. You have to have this just work
and browse to the data + fields without having to do any SQL mapping. I
mean I think SQL mapping features that most pick databases offer are really
fantastic, but the problem is is a lot of the users don't want to really
work with and have to learn any SQL. Right now, they have to learn sql to
make this work.

So providing users with an easy to use system (and possibly familiar) system
like access for building forms, building reports etc, and not having to
learn to set up the ODBC SQL mapping stuff on the pick side is the real goal
and issue here. I don't want the user to have to do a bunch of little
transforms, wrote code, or play with having to send/upload a bunch of csv
files.

You have long time pick users, they know the file, they know the field
(attribute) names and likely been using it for years. Just display the
choices of databases and let me work or edit that data. Let me build my
forms and work with the data. Same thing for reports. ms-Access is always
been great because you really don't have to work directly with the SQL if
you don't want to. I suppose for those, the query builder does now allow tcl
commands in access anyway.

In fact, it's been about a year, but I don't even think you have to make the
query pass-though, but can go:


select FirstName,LastName from customers (sql in access)
list custtomers FirstName,LastName (tcl - you just precede the text with
> and it consider tcl not sql)

So, even for a linked table, I belive the above works in d3.

As I mentioned about AccuTerm, there are some
established methods of doing this already and AccuTerm is widely used,
so there is some competition.

Absolutely. And, as mentioned there is also the SQL mapping commands. If you
know how to setup ODBC to your pick system now, you can well use Access
directly with most of your data now. The only barrier here is that learning
curve and setting up of the connections to your data on the Pick side and
doing the sql mapping commands.

At the end of the day, I can't really say there's enough interest here. I
think a lot of the people that want to work directly with some of the pick
data are not just employees who use some application in the organization.
However, those longer experienced users that are allowed to go to tcl are
likely the ones that would benefit from this. However it would also give
out reduced learning curve for many of the new employees or coworkers two
start adopting and working with some of the data locked up in those pick
systems.

Anyway, hopefully the next version Access WEB Services supports the BDC, as
then I will do some demos here of editing pick data on a web site with forms
built using ms-access.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKemail@anonym

"hbkeultjes" <email@anonym; wrote in message
news:email@anonym...
Pardon the Greenhorn questions and comments:

Will not HTML-5 be a greater enabler of Pick screen input and output
and thus require fewer hoops to be jumped through to enable a GUI
interface to Pick?

Hum, fewer hoops? Perhaps.

The problem as always is you then need to start attaching code to things.

Code for the combo box, or code for a button on the form, and HOW you do
that is the issue here.

The tools today are quite good at attaching data to a form that will
ultimately run in a browser.

The issue of keeping the presentation layer separate from the data is
certainly an important issue. That is very much the norm in the industry now
anyway.

Ultimately, all I care about is having that application run for the user, be
it a green screen, a rich client program on the desktop or something that
runs in a web browser. Today if you not moving towards the web, you not in
the game.

I think larger then the issue then separating out the data from the
presentation layer (user interface) is how you going to develop and tie all
that stuff together?

People do VERY little hand coding of HTML. In 99% of the cases, they use
some type of screen editor development system. In other words even today I
don't think people even hand code their data input screens in Pick (do
they??).

Even with Pick you be using some system builder product like screen gen or
system builder or whatever. Perhaps some of the Pick legacy code one has
with hand written input screens will be tweaked and maintained from time to
time, but that is not the norm.

However, today, be it ms-access, or even Visual Studio, you don't write a
bunch of code for a input screen. You simply drag + drop controls on a
screen and you are done. The only code you write will be attaching code to
the buttons etc. Same goes for most report building. So, what is underlying
is not super important.

So, when you building that input form, you not going to be hand coding HTML,
or writing code to build the basic layout of the screen. So, what do you
care if the screen building input tool creates XAML or HTML version 5 or 17?
If a company is writing all their code with HTML in a notepad, then they
doing it the wrong way.

So it is a great point on your part that those development tools might start
to use HTML 5, or some markup language that makes it easy to separate out
the data from the display, but as a developer, it not a real issue so to
speak.

The developer not going to be editing that HTML directly (and, by the way,
you never suggested as such anyway). To be fair you WILL often go into the
mark up language in many modern development system. And, the reason for this
is often the need to cut + paste the markup language, or to tweak things a
bit. Most good systems today allow a flipping between the markup and the GUI
layout and design mode anyway.

At the end of the day how the underlying systems are very important but it
really comes down to the cost of development and that means we are not hand
coding the bulk of these systems anymore.

The reason why products like Ms-Access is so popular is the issue of data
binding the forms. When your just editing a record on a single computer
desktop, this is simple. When you start connecting to a database server,
then it becomes more complex. And, then when you tie the screen form editor
with some language like HTML for the web, and again have to bind that to
data sources. This can be a lot of work, and it mostly about connecting up
data to the form you are building.

Of course, you do often have to adopt underlying technologies to solve
problems. For example when you are editing data in that web form, and you
tab to the next text box, or click on a combo box, is that code going to run
on your desktop (inside of your browser as JavaScript), or do you have to
wait for a round trip to the sever and have the code run there to make that
combo box drop down and have the server side the update the HTML display?
So, now, you need a way to give a developer a choice as to where the code
going to run (on desktop in browser, or server side). So, now one needs a
way to let developers choose where that code going to run.

That is why you hear the buzz-word AJAX. The idea here is to get some code
to run in the browser so the application is VERY responsive like a windows
desktop program. Thus, having SOME code run in the browser is a good thing,
but the problem then becomes how you let the developer decide where the code
he is just writing is going to run? For example, for ms-access forms that
run on the web, the code in that form actually runs browser side (it gets
converted into JavaScript).

Pick is often easy because you don't' deal with a bunch of connection
strings or lays of systems just to read a simple record. In pick you just
simply execute a OPEN command to open a file and you off and running.

Regardless of above issues, it is the set of tools that allows one to be
productive, and it is the job of those tools to tie together the data and
presentation. In fact, the better job that tool does in this data binding
area very much defines how productive you will be as a developer. The new
tools are becoming really very good in this area now.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKemail@anonym

"Ross Ferris" <email@anonym; wrote in message
news:email@anonym...
Albert,

Have you been studying a video produced by TG on how to make posts in
this forum :-)

LOL....

Hey, we needed some excitement here...it was becoming a bit too quiet here!


I'm obviously biased towards HTML, but that is because we have spent
so long in joining all of the dots with Visage. I believe things like
lock concurrency may present another set of of issues that would need
to be addressed within Access

Keep in mind when we are talking about ms-access WEB services. We thus are
talking about a 100% cloud based architecture built around all of the latest
tools out of Redmond.

The fact that you build the Access WEB form on your desktop inside of the
ms-access client really doesn't matter anymore. When you take that Access
form, and hit the publish button it it results in a .net form (XAML - or
what is called zammel).

When you speak of concurrency, are you talking about some record locking
mechanism, or that of scalabilityh?

Keep in mind that the code you write in that ms-access form actually gets
converted to JavaScript and winds up running in your browser on the desktop.
Your back end code in the ms-access application gets converted into
SharePoint workflows. So access web services is a true multi-teir
development system. In fact the application thus runs on Microsoft's new
cloud computing initiative. We not talking about 100's, or even 1000's of
users here, but 10's of thousands of users.

The new Access WEB Services requires SharePoint Services. Microsoft will be
hosting and providing cloud editions of this architecture. So, this is
designed to scale in a huge way...a VERY huge way.

So I'm not sure what your reference is to in terms of currency or
scalability here?

I suspect I'm just misunderstanding your point. While I'm learning lots, I
am fairly new to the web development stuff.

I certainly would not make the claim that the new Access WEB Services is a
high end development system. It is not. However, if you look at the above
video, building that kind of application using other web based development
tools would have taken considerable more work then the time it took me to
bang that out that application using the Access desktop client, yet the
results do run in any browser, and the results are very scalable.

Keep in mind when that applications running on the web, none of the data are
resides in what one would refer to the jet or so call file based data engine
that access has had for about 17 years now.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKemail@anonym