Re: Acces To SQL - Exporting To Comp Without SQL - Possible Or Not ?

Forgive Daniel. He hates Access, hence his terse reply.

It is entirely possible to develop an Access front-end to talk to an Oracle (or other major database) on a different machine (a server). But your teachers are correct that Access forms need to get their data from *somewhere*, which means setting up a link from the PC running Access to the server running Oracle. That link is most easily an ODBC one.

ODBC links for an Access form or query can be specified programmatically using code like the following:

    Dim dbsCurrent As Database
    Dim qdfPassThrough As QueryDef
    Dim qdfLocal As QueryDef
    Dim rstTopFive As Recordset
    Dim strMessage As String

    Set dbsCurrent = CurrentDb
    Set qdfPassThrough = dbsCurrent.CreateQueryDef("qryAreas")     qdfPassThrough.Connect =
"ODBC;DSN=db9;UID=COMPOSER;PWD=UNKNOWN;DBQ=DB9;DBA=W;APA=T;EXC=T;XSM=Owner;F EN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;M DI=F;CSR=F;FWC=F;PFC=10;TLO=0;"
    qdfPassThrough.SQL = "select * from composer.areas order by areacode;"     qdfPassThrough.ReturnsRecords = True     dbsCurrent.Close

This is a bit of code I use to create a pass-through query on a table called "Areas". Once the query has been created, I can then build an Access form on that query. Unfortunately, pass-through queries are read-only, so editing the data can't use this method (hint: you have a form which isn't bound to anything, let the user enter appropriate data, and when they click a "SAVE RECORD" button, you gather all the entries as variables and construct a new pass-through query that reads 'insert into areas...' or 'update areas...'.

The key bit there is the 'qdfPassThrough.Connect' reference (which will no doubt wrap like crazy when you read this, but is in fact all on one line). It tells Access to use ODBC, to look for a database called DB9, to connect to that database as user COMPOSER, password "UNKNOWN". You can read up what all the other bits and pieces mean elsewhere.

The trouble with this approach is the ODBC link. You must use an Oracle ODBC driver to make that connection, and that *requires* you to install the Oracle client (not the complete database, just the client software -but that's still several hundred megabytes of install).

When you use Access like this you have to have a mental gear change. The application code belongs in the backend Oracle database as triggers or procedures -it runs faster and more reliably that way. What you code in Access should be very lightweight stuff to do with screen handling issues, error messages, trapping server errors and converting them to something more friendly, and so on. That means you need to start learning PL/SQL or (conceivably) Java, since these are the languages of the backend.

In short, what you want to do is entirely possible, but it does require some client PC installation, and a change in development practice from what you are probably used to.

Regards
HJR "Lovely Angel For You" <lovely_angel_for_you_at_yahoo.com> wrote in message news:55295a7e.0207041004.35535a80_at_posting.google.com...
> Dear Friends
> Hope you all doing great.
>
> I have a query regarding use of SQL in my software.
> I am having a softare in which I use VB as frontend and Access at
> backend. When I create installer package for this project I include
> the dependencies files and drivers required to run Access database.
> This enable me to install my software on any computer whether the
> computer has MS Access installed or not.
> And I am able to run this software easily.
>
> Now I want to use SQL or any other database package instead of Access.
> And I also want that I am able to install it to any computer
> regardless of whether that computer has SQL installed or not. I should
> be able to run the software without installing SQL or any other
> database package. Same as I do with Access project.
>
> But as per my research and knowledge of my teachers it is not
> possible. They say you have to install SQL. One of them say even if I
> am able to run the software without installing SQL and exporting the
> drivers required I still have to create DSN.
>
> I am ready to that provided I dont have to install SQL. But I also
> intend to distribute this software to novice users who doesnt know how
> to create DSN. So I want DSN creation is done at the back from within
> the code while installing so that user doesnt have to do anything and
> user doesnt come to know that I have created a DSN.
>
> This is what the problem is. Any help will be appreciated.
>
> In brief
>
> Proj with MS Access --> Export drivers required with installer -->
> install on on any computer --> run on computer with MS Access.
>
> Proj with SQL --> is whole of the above process possible.
>
> Please let me know of this.
>
> Waiting......
>
> Love
> Lovely
>
> ====================================
Received on Thu Jul 04 2002 - 17:36:54 CDT