Client/Server Development with Access 2003

[Guest]

I have two questions:

1. I am unable to edit tables in my SQL Server Express from within my
Access front end project. I get an error message saying that my Access 2003
doesn't support that functionality. Is that really true?

2. My front end needs to get data from 2 different SQL Server backends. I
don't have any trouble switching back and forth between connections, but in
the case of the secondary connection I am unable to use the data in a
RecordSet as the RowSource for a ComboBox in a Form. What's the trick?

 

[aaron.kempf]

1) SOME editing in SQL 2005 works. like sprocs for example... some
sprocs; mainly in SQL view-- allow you to edit in ADP. Most table-edit
functionality doesn't work.

I had heard that Microsoft is going to release a patch for this... i
think that ADP is a wonderful tool; in an important segment.. and I
think that Microsoft now takes ADP seriously.

2). I haven't used the recordset functionality-- because of stability
concerns. If i were you; I would highlight the ROWSOURCETYPE---
again.. ROWSOURCETYPE because you can put the name of a vba function in
there and it supports simple binding.. your function does need to
follow a particular format. Hit F1 when you have the ROWSOURCETYPE
property and you should be able to find something similar to this:


RowSourceType Property (User-Defined Function) - Code Argument Values
The Visual Basic function you create must accept five arguments. The
first argument must be declared as a control and the remaining
arguments as Variants. The function itself must return a Variant.

Function functionname (fld As Control, id As Variant, row As Variant,
col As Variant, code As Variant) As Variant

The Function procedure has the following five required arguments.

Argument Description
fld A control variable that refers to the list box or combo box being
filled.
id A unique value that identifies the control being filled. This is
useful when you want to use the same user-defined function for more
than one list box or combo box and must distinguish between them. (The
example sets this variable to the value of the Timer function.)
row The row being filled (zero-based).
col The column being filled (zero-based).
code An intrinsic constant that specifies the kind of information being
requested.


Note Because Microsoft Access calls a user-defined function several
times to insert items into a list, often you must preserve information
from call to call. The best way to do this is to use Static variables.

Microsoft Access calls the user-defined function by repeatedly using
different values in the code argument to specify the information it
needs. The code argument can use the following intrinsic constants.

Constant Meaning Function returns
acLBInitialize Initialize Nonzero if the function can fill the list;
False (0) or Null otherwise.
acLBOpen Open Nonzero ID value if the function can fill the list; False
or Null otherwise.
acLBGetRowCount Number of rows Number of rows in the list (can be
zero); -1 if unknown.
acLBGetColumnCount Number of columns Number of columns in the list
(can't be zero); must match the property sheet value.
acLBGetColumnWidth Column width Width (in twips) of the column
specified by the col argument; -1 to use the default width.
acLBGetValue List entry List entry to be displayed in the row and
column specified by the row and col arguments.
acLBGetFormat Format string Format string to be used to format the list
entry displayed in the row and column specified by the row and col
arguments; -1 to use the default format.
acLBEnd End (the last call to a user-defined function always uses this
value) Nothing.
acLBClose (Not used) Not used.


Microsoft Access calls your user-defined function once for
acLBInitialize, acLBOpen, acLBGetRowCount, and acLBGetColumnCount. It
initializes the user-defined function, opens the query, and determines
the number of rows and columns.

Microsoft Access calls your user-defined function twice for
acLBGetColumnWidth - once to determine the total width of the list
box or combo box and a second time to set the column width.

The number of times your user-defined function is called for
acLBGetValue and acLBGetFormat to get list entries and to format
strings varies depending on the number of entries, the user's
scrolling, and other factors.

Microsoft Access calls the user-defined function for acLBEnd when the
form is closed or each time the list box or combo box is queried.

Whenever a particular value (such as the number of columns) is
required, returning Null or any invalid value causes Microsoft Access
to stop calling the user-defined function with that code.

Tip You can use the Select Case code structure from the example as a
template for your own RowSourceType property user-defined functions.

 

[Albert D. Kallal]

I have two questions:

1. I am unable to edit tables in my SQL Server Express from within my
Access front end project. I get an error message saying that my Access
2003
doesn't support that functionality. Is that really true?

Well, ms-access 2003 was out before the new version of sql express. So, for
support
of ADP projects, you have to wait until a2007 comes out.

However, the above ONLY APPLIES if you are using a adp project. If you use
a standard mdb ms-access file/application, you can simply use linked tables
via
ODBC, and the above is not a problem at all..

2. My front end needs to get data from 2 different SQL Server backends.
I
don't have any trouble switching back and forth between connections, but
in
the case of the secondary connection I am unable to use the data in a
RecordSet as the RowSource for a ComboBox in a Form. What's the trick?

If you use a mdb, and linked tables, the you can simply add linked table,
and there
is no restrictions on what data source the data comes from (eg: you could
have the
data come form a local mdb file, a FoxPro file, or any server system, be it
oracle, sql server...or sql server express....

So, don't use a ADP project, but use a mdb file, and linked tables.

 

[Guest]

Thank you.

With respect to my second question, I do know about linked files. But I am
still hoping to solve my problem using an ADP Project.

 

[aaron.kempf]

Albert;

I disagree.

'well access came out before sql server'

do you remember what happened with Access 2000? and SQL 2000?

do you remember how the dipshits in Redmond shipped Access 2000 and it
was incompatable with SQL 2000?

your logic is incorrect.

you are a big fat lazy bastard; I hope you choke and die on an errant
MDB file soon.

have you ever even used MDB?

completely UNRELIABLE.

You SHOULD just link the SQL Servers together.. I mean--friggin
RIGHTCLICK LINK in the ADP.

-Aaron

 

[aaron.kempf]

linked ODBC tables.. shit albert

have you heard of this invention called the 1990s?
you're still stuck in the 80s kid

LINK THE SERVERS TOGETHER
LINK THE SERVERS TOGETHER
LINK THE SERVERS TOGETHER
LINK THE SERVERS TOGETHER
LINK THE SERVERS TOGETHER


100 times more efficient than linked tables you only need to edit
linking information in ONE PLACE not in 100 different tables

 

[Albert D. Kallal]

With respect to my second question, I do know about linked files. But I

am
still hoping to solve my problem using an ADP Project.

I addressed that. You can as far as I know use a adp project, but you will
not be able to use the table manager, and
setup relations from the ms-access UI.

So, what this means is that your development approach of a a2003 adp + sql
server express is going to be the same
as using VB.net + sql server express, or a2003 mdb + sql server express.

In other words, for the most part, developers use the sql server management
tools + ms-access. It was always a "nice" bonus that ADP projects could
manage sql server.

However, since sql server express is new, then even the EXISTING sql
enterprise management tools DO NOT work with sql express. There is no future
plans to update the *EXISTING* enterprise manger, nor access 2000, 2002, or
2003 adp's to work with the new version of sql express.

The suggestion solutions we have are

- don't use a adp, as it is TIGHTLY tied to the version of sql server.
(or, conversely - use the correct version of sql server for the given
type of adp).

- if you must use adp, then use the express tools to manage tables +
relationships, and not the table
design tools in ms-access (adp).

- Use odbc linked tables, and you can use access 95, 97, 2000, 2002,
2003, and 2007 with sql server, or with orcale, or with sql server express.
There is no lock in when you use linked tables.

So, you can still use ms-access and ADP on sql express, but you not going
to be able to use the table management tools. Access 2007 adp's will support
sql server express, and thus the functionally of the table management tools
will return to ms-access adp's.

As it stands now, pre 2007 adp's are designed to work with sql server. I not
tried, but I do recall there is a way to kick sql server express into a
compatibility mode..and that may very well work with a2003 adp's....but, I
just don't know about this feature/ability.

 

[aaron.kempf]

Albert;

you are a liar and a jerk.

THINK OUTSIDE THE BOX!!!

develop in SQL 2000 using ADP. Migrate it to 2005 when you're done.

-Aaron