Access/jet to SQL Server

We have a client who has an urgent need for one of our "packaged" apps to be
used with full SQL Server (not just MSDE) and not Jet and as we know nothing
in this area we have some questions and any help is appreciated:
1. Our app uses Access Security with an mdw file for development which is
not distributed as the app is accessed by the user's own system.mdw file as
we have a couple of tables blocked (i.e. hold registration info etc) and
only accessed by RWOP queries in the FE. Can this still be done with SQL?
2. Some of our tables start with "Usys" and it seems the wizard will not
acknowledge them. What is the best way to address this?
3. Would we have to have different FE's i.e. one for a jet back end and one
for a SQL back end or can one FE be used with both and if so how could we do
it?
4. For us to start reading up on all of this would take 6 months and we
don't have that luxury so is there any "summary" documents or sites that
would help us?
5. Any other tips, suggestions, help etc that you could advise that we
should be mindful of?

Thanks for any advice you may be able to give

Regards
Ian Baker


0
Ian
2/13/2004 6:41:04 AM
access.conversion 3038 articles. 0 followers. Follow

7 Replies
891 Views

Similar Articles

[PageSpeed] 41

0) I would not accept this project as 'urgent' if you have
never done it before.

1) RWOP doesn't work with SQL tables. You will have to
rethink your security model and copy protection.

2) Rename the Usys tables. You can rename the table links as
Usys... and connect them to the new SQL Server tables.

3) You can use on FE for both Jet and SQL Server BE: you
can attach linked tables to either Jet or SQL Server.

4) If you have never done this before, at least look at
the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/

5) If you add data to subforms you will have to modify
your data entry process.  If you use complex transactions
they won't work. If you use A97 you will have trouble
working out how to connect to SS2000.

(david)


"Ian Baker" <ian@companyabc.com> wrote in message
news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> We have a client who has an urgent need for one of our "packaged" apps to
be
> used with full SQL Server (not just MSDE) and not Jet and as we know
nothing
> in this area we have some questions and any help is appreciated:
> 1. Our app uses Access Security with an mdw file for development which is
> not distributed as the app is accessed by the user's own system.mdw file
as
> we have a couple of tables blocked (i.e. hold registration info etc) and
> only accessed by RWOP queries in the FE. Can this still be done with SQL?
> 2. Some of our tables start with "Usys" and it seems the wizard will not
> acknowledge them. What is the best way to address this?
> 3. Would we have to have different FE's i.e. one for a jet back end and
one
> for a SQL back end or can one FE be used with both and if so how could we
do
> it?
> 4. For us to start reading up on all of this would take 6 months and we
> don't have that luxury so is there any "summary" documents or sites that
> would help us?
> 5. Any other tips, suggestions, help etc that you could advise that we
> should be mindful of?
>
> Thanks for any advice you may be able to give
>
> Regards
> Ian Baker
>
>


0
david
2/13/2004 7:56:58 AM
I agree.
Don't take this on unless you have time to do it right.
It sounds like a disaster wiating to happen right now.

I suggest you hire someone with SQL Server experience to do the conversion
and design correctly.
(Not me.)
-- 
Joe Fallon
Access MVP



"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> 0) I would not accept this project as 'urgent' if you have
> never done it before.
>
> 1) RWOP doesn't work with SQL tables. You will have to
> rethink your security model and copy protection.
>
> 2) Rename the Usys tables. You can rename the table links as
> Usys... and connect them to the new SQL Server tables.
>
> 3) You can use on FE for both Jet and SQL Server BE: you
> can attach linked tables to either Jet or SQL Server.
>
> 4) If you have never done this before, at least look at
> the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/
>
> 5) If you add data to subforms you will have to modify
> your data entry process.  If you use complex transactions
> they won't work. If you use A97 you will have trouble
> working out how to connect to SS2000.
>
> (david)
>
>
> "Ian Baker" <ian@companyabc.com> wrote in message
> news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > We have a client who has an urgent need for one of our "packaged" apps
to
> be
> > used with full SQL Server (not just MSDE) and not Jet and as we know
> nothing
> > in this area we have some questions and any help is appreciated:
> > 1. Our app uses Access Security with an mdw file for development which
is
> > not distributed as the app is accessed by the user's own system.mdw file
> as
> > we have a couple of tables blocked (i.e. hold registration info etc) and
> > only accessed by RWOP queries in the FE. Can this still be done with
SQL?
> > 2. Some of our tables start with "Usys" and it seems the wizard will not
> > acknowledge them. What is the best way to address this?
> > 3. Would we have to have different FE's i.e. one for a jet back end and
> one
> > for a SQL back end or can one FE be used with both and if so how could
we
> do
> > it?
> > 4. For us to start reading up on all of this would take 6 months and we
> > don't have that luxury so is there any "summary" documents or sites that
> > would help us?
> > 5. Any other tips, suggestions, help etc that you could advise that we
> > should be mindful of?
> >
> > Thanks for any advice you may be able to give
> >
> > Regards
> > Ian Baker
> >
> >
>
>


0
Joe
2/14/2004 5:10:31 AM
Thanks Guys
Ok, I must be missing something huge here as I just installed SQL Server
(Dev), opened my BE using my Developer.mdw, upsized all tables to SQL Server
(using A2003 although the app is released in A2k & A2k2) accepting all the
defaults - everything ok. Open my FE, deleted all the jet table links,
created new table links using ODBC to the SQL Server db, renamed the table
links removing the dbo_ from each and tested and all seems ok - no problems
yet.

The security that I have is really to stop basic users from "playing" with a
registration table but I have no problems with an administrator having
access to the tables. The only I have now is:
1. What am I missing???????
2. How do I include & package the SQL backend (I am using the PDW) - perhaps
this is where the 6 months of reading will come into it.
3. I have to figure how to allow the user that when starting the FE to
select either "link to mdb" or "link to SQL Server" and the code to handle
the linking process.

Thanks again!
-- 
Regards
Ian
-
"Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
news:%23iZPbjr8DHA.2672@TK2MSFTNGP10.phx.gbl...
> I agree.
> Don't take this on unless you have time to do it right.
> It sounds like a disaster wiating to happen right now.
>
> I suggest you hire someone with SQL Server experience to do the conversion
> and design correctly.
> (Not me.)
> -- 
> Joe Fallon
> Access MVP
>
>
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> > 0) I would not accept this project as 'urgent' if you have
> > never done it before.
> >
> > 1) RWOP doesn't work with SQL tables. You will have to
> > rethink your security model and copy protection.
> >
> > 2) Rename the Usys tables. You can rename the table links as
> > Usys... and connect them to the new SQL Server tables.
> >
> > 3) You can use on FE for both Jet and SQL Server BE: you
> > can attach linked tables to either Jet or SQL Server.
> >
> > 4) If you have never done this before, at least look at
> > the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/
> >
> > 5) If you add data to subforms you will have to modify
> > your data entry process.  If you use complex transactions
> > they won't work. If you use A97 you will have trouble
> > working out how to connect to SS2000.
> >
> > (david)
> >
> >
> > "Ian Baker" <ian@companyabc.com> wrote in message
> > news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > > We have a client who has an urgent need for one of our "packaged" apps
> to
> > be
> > > used with full SQL Server (not just MSDE) and not Jet and as we know
> > nothing
> > > in this area we have some questions and any help is appreciated:
> > > 1. Our app uses Access Security with an mdw file for development which
> is
> > > not distributed as the app is accessed by the user's own system.mdw
file
> > as
> > > we have a couple of tables blocked (i.e. hold registration info etc)
and
> > > only accessed by RWOP queries in the FE. Can this still be done with
> SQL?
> > > 2. Some of our tables start with "Usys" and it seems the wizard will
not
> > > acknowledge them. What is the best way to address this?
> > > 3. Would we have to have different FE's i.e. one for a jet back end
and
> > one
> > > for a SQL back end or can one FE be used with both and if so how could
> we
> > do
> > > it?
> > > 4. For us to start reading up on all of this would take 6 months and
we
> > > don't have that luxury so is there any "summary" documents or sites
that
> > > would help us?
> > > 5. Any other tips, suggestions, help etc that you could advise that we
> > > should be mindful of?
> > >
> > > Thanks for any advice you may be able to give
> > >
> > > Regards
> > > Ian Baker
> > >
> > >
> >
> >
>
>


0
Ian
2/14/2004 11:09:00 PM
> 1. What am I missing???????

The system is designed for bog standard applications (apart
from data problems and the autonumber problem with subforms).
If you have a bog standard application you may have escaped
with no problems at all.

> 2. How do I include & package the SQL backend

Any client that is using SQL Server will have a DBA that
will want to be part of the process.  You can distribute
a SQL Server database as a .dbf file, and the DBA can
load it into SQL Server and set up the user permissions.

> 3. I have to figure how to allow the user that when starting the FE
> select either "link to mdb" or "link to SQL Server" and the code to

A radio button on your re-link form.......  Look at the connect
properties of one of your upsized linked tables to see what the
new connect property should look like.

(david)



"Ian Baker" <ian@companyabc.com> wrote in message
news:%23wk%23P%2308DHA.1632@TK2MSFTNGP12.phx.gbl...
> Thanks Guys
> Ok, I must be missing something huge here as I just installed SQL Server
> (Dev), opened my BE using my Developer.mdw, upsized all tables to SQL
Server
> (using A2003 although the app is released in A2k & A2k2) accepting all the
> defaults - everything ok. Open my FE, deleted all the jet table links,
> created new table links using ODBC to the SQL Server db, renamed the table
> links removing the dbo_ from each and tested and all seems ok - no
problems
> yet.
>
> The security that I have is really to stop basic users from "playing" with
a
> registration table but I have no problems with an administrator having
> access to the tables. The only I have now is:
> 1. What am I missing???????
> 2. How do I include & package the SQL backend (I am using the PDW) -
perhaps
> this is where the 6 months of reading will come into it.
> 3. I have to figure how to allow the user that when starting the FE to
> select either "link to mdb" or "link to SQL Server" and the code to handle
> the linking process.
>
> Thanks again!
> -- 
> Regards
> Ian
> -
> "Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
> news:%23iZPbjr8DHA.2672@TK2MSFTNGP10.phx.gbl...
> > I agree.
> > Don't take this on unless you have time to do it right.
> > It sounds like a disaster wiating to happen right now.
> >
> > I suggest you hire someone with SQL Server experience to do the
conversion
> > and design correctly.
> > (Not me.)
> > -- 
> > Joe Fallon
> > Access MVP
> >
> >
> >
> > "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> > news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> > > 0) I would not accept this project as 'urgent' if you have
> > > never done it before.
> > >
> > > 1) RWOP doesn't work with SQL tables. You will have to
> > > rethink your security model and copy protection.
> > >
> > > 2) Rename the Usys tables. You can rename the table links as
> > > Usys... and connect them to the new SQL Server tables.
> > >
> > > 3) You can use on FE for both Jet and SQL Server BE: you
> > > can attach linked tables to either Jet or SQL Server.
> > >
> > > 4) If you have never done this before, at least look at
> > > the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/
> > >
> > > 5) If you add data to subforms you will have to modify
> > > your data entry process.  If you use complex transactions
> > > they won't work. If you use A97 you will have trouble
> > > working out how to connect to SS2000.
> > >
> > > (david)
> > >
> > >
> > > "Ian Baker" <ian@companyabc.com> wrote in message
> > > news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > > > We have a client who has an urgent need for one of our "packaged"
apps
> > to
> > > be
> > > > used with full SQL Server (not just MSDE) and not Jet and as we know
> > > nothing
> > > > in this area we have some questions and any help is appreciated:
> > > > 1. Our app uses Access Security with an mdw file for development
which
> > is
> > > > not distributed as the app is accessed by the user's own system.mdw
> file
> > > as
> > > > we have a couple of tables blocked (i.e. hold registration info etc)
> and
> > > > only accessed by RWOP queries in the FE. Can this still be done with
> > SQL?
> > > > 2. Some of our tables start with "Usys" and it seems the wizard will
> not
> > > > acknowledge them. What is the best way to address this?
> > > > 3. Would we have to have different FE's i.e. one for a jet back end
> and
> > > one
> > > > for a SQL back end or can one FE be used with both and if so how
could
> > we
> > > do
> > > > it?
> > > > 4. For us to start reading up on all of this would take 6 months and
> we
> > > > don't have that luxury so is there any "summary" documents or sites
> that
> > > > would help us?
> > > > 5. Any other tips, suggestions, help etc that you could advise that
we
> > > > should be mindful of?
> > > >
> > > > Thanks for any advice you may be able to give
> > > >
> > > > Regards
> > > > Ian Baker
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
david
2/15/2004 6:55:05 AM
Hi David
"bog standard applications" - ?
The application is called Jackaroo IT with a 30 day trial available for
download at http://jackaroo.net.au and contains 60 tables, 300 queries (both
hard and SQL code), 60 forms (inc 22 subs), 18,000 lines of code and 2
ActiveX controls. We have clients using it with 26 concurrent users using
Jet but the new client wants to put a "light version" on all employee
desktops so the old Jet just won't take it.

-- 
Regards
Ian
-
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:Om27%23C58DHA.1632@TK2MSFTNGP12.phx.gbl...
> > 1. What am I missing???????
>
> The system is designed for bog standard applications (apart
> from data problems and the autonumber problem with subforms).
> If you have a bog standard application you may have escaped
> with no problems at all.
>
> > 2. How do I include & package the SQL backend
>
> Any client that is using SQL Server will have a DBA that
> will want to be part of the process.  You can distribute
> a SQL Server database as a .dbf file, and the DBA can
> load it into SQL Server and set up the user permissions.
>
> > 3. I have to figure how to allow the user that when starting the FE
> > select either "link to mdb" or "link to SQL Server" and the code to
>
> A radio button on your re-link form.......  Look at the connect
> properties of one of your upsized linked tables to see what the
> new connect property should look like.
>
> (david)
>
>
>
> "Ian Baker" <ian@companyabc.com> wrote in message
> news:%23wk%23P%2308DHA.1632@TK2MSFTNGP12.phx.gbl...
> > Thanks Guys
> > Ok, I must be missing something huge here as I just installed SQL Server
> > (Dev), opened my BE using my Developer.mdw, upsized all tables to SQL
> Server
> > (using A2003 although the app is released in A2k & A2k2) accepting all
the
> > defaults - everything ok. Open my FE, deleted all the jet table links,
> > created new table links using ODBC to the SQL Server db, renamed the
table
> > links removing the dbo_ from each and tested and all seems ok - no
> problems
> > yet.
> >
> > The security that I have is really to stop basic users from "playing"
with
> a
> > registration table but I have no problems with an administrator having
> > access to the tables. The only I have now is:
> > 1. What am I missing???????
> > 2. How do I include & package the SQL backend (I am using the PDW) -
> perhaps
> > this is where the 6 months of reading will come into it.
> > 3. I have to figure how to allow the user that when starting the FE to
> > select either "link to mdb" or "link to SQL Server" and the code to
handle
> > the linking process.
> >
> > Thanks again!
> > -- 
> > Regards
> > Ian
> > -
> > "Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
> > news:%23iZPbjr8DHA.2672@TK2MSFTNGP10.phx.gbl...
> > > I agree.
> > > Don't take this on unless you have time to do it right.
> > > It sounds like a disaster wiating to happen right now.
> > >
> > > I suggest you hire someone with SQL Server experience to do the
> conversion
> > > and design correctly.
> > > (Not me.)
> > > -- 
> > > Joe Fallon
> > > Access MVP
> > >
> > >
> > >
> > > "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> > > news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> > > > 0) I would not accept this project as 'urgent' if you have
> > > > never done it before.
> > > >
> > > > 1) RWOP doesn't work with SQL tables. You will have to
> > > > rethink your security model and copy protection.
> > > >
> > > > 2) Rename the Usys tables. You can rename the table links as
> > > > Usys... and connect them to the new SQL Server tables.
> > > >
> > > > 3) You can use on FE for both Jet and SQL Server BE: you
> > > > can attach linked tables to either Jet or SQL Server.
> > > >
> > > > 4) If you have never done this before, at least look at
> > > > the SQL upsizer tool from SSW http://www.ssw.com.au/ssw/UpsizingPRO/
> > > >
> > > > 5) If you add data to subforms you will have to modify
> > > > your data entry process.  If you use complex transactions
> > > > they won't work. If you use A97 you will have trouble
> > > > working out how to connect to SS2000.
> > > >
> > > > (david)
> > > >
> > > >
> > > > "Ian Baker" <ian@companyabc.com> wrote in message
> > > > news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > > > > We have a client who has an urgent need for one of our "packaged"
> apps
> > > to
> > > > be
> > > > > used with full SQL Server (not just MSDE) and not Jet and as we
know
> > > > nothing
> > > > > in this area we have some questions and any help is appreciated:
> > > > > 1. Our app uses Access Security with an mdw file for development
> which
> > > is
> > > > > not distributed as the app is accessed by the user's own
system.mdw
> > file
> > > > as
> > > > > we have a couple of tables blocked (i.e. hold registration info
etc)
> > and
> > > > > only accessed by RWOP queries in the FE. Can this still be done
with
> > > SQL?
> > > > > 2. Some of our tables start with "Usys" and it seems the wizard
will
> > not
> > > > > acknowledge them. What is the best way to address this?
> > > > > 3. Would we have to have different FE's i.e. one for a jet back
end
> > and
> > > > one
> > > > > for a SQL back end or can one FE be used with both and if so how
> could
> > > we
> > > > do
> > > > > it?
> > > > > 4. For us to start reading up on all of this would take 6 months
and
> > we
> > > > > don't have that luxury so is there any "summary" documents or
sites
> > that
> > > > > would help us?
> > > > > 5. Any other tips, suggestions, help etc that you could advise
that
> we
> > > > > should be mindful of?
> > > > >
> > > > > Thanks for any advice you may be able to give
> > > > >
> > > > > Regards
> > > > > Ian Baker
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
Ian
2/16/2004 12:37:50 AM
> "bog standard applications" - ?

It not the size of the application that matters so much
as the coding practice.  Although with an application
that size you are less likely to have some of the variety
that you might get within a bigger application.

(david)



"Ian Baker" <ian@companyabc.com> wrote in message
news:OLWbiUC9DHA.2576@TK2MSFTNGP11.phx.gbl...
> Hi David
> "bog standard applications" - ?
> The application is called Jackaroo IT with a 30 day trial available for
> download at http://jackaroo.net.au and contains 60 tables, 300 queries
(both
> hard and SQL code), 60 forms (inc 22 subs), 18,000 lines of code and 2
> ActiveX controls. We have clients using it with 26 concurrent users using
> Jet but the new client wants to put a "light version" on all employee
> desktops so the old Jet just won't take it.
>
> -- 
> Regards
> Ian
> -
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:Om27%23C58DHA.1632@TK2MSFTNGP12.phx.gbl...
> > > 1. What am I missing???????
> >
> > The system is designed for bog standard applications (apart
> > from data problems and the autonumber problem with subforms).
> > If you have a bog standard application you may have escaped
> > with no problems at all.
> >
> > > 2. How do I include & package the SQL backend
> >
> > Any client that is using SQL Server will have a DBA that
> > will want to be part of the process.  You can distribute
> > a SQL Server database as a .dbf file, and the DBA can
> > load it into SQL Server and set up the user permissions.
> >
> > > 3. I have to figure how to allow the user that when starting the FE
> > > select either "link to mdb" or "link to SQL Server" and the code to
> >
> > A radio button on your re-link form.......  Look at the connect
> > properties of one of your upsized linked tables to see what the
> > new connect property should look like.
> >
> > (david)
> >
> >
> >
> > "Ian Baker" <ian@companyabc.com> wrote in message
> > news:%23wk%23P%2308DHA.1632@TK2MSFTNGP12.phx.gbl...
> > > Thanks Guys
> > > Ok, I must be missing something huge here as I just installed SQL
Server
> > > (Dev), opened my BE using my Developer.mdw, upsized all tables to SQL
> > Server
> > > (using A2003 although the app is released in A2k & A2k2) accepting all
> the
> > > defaults - everything ok. Open my FE, deleted all the jet table links,
> > > created new table links using ODBC to the SQL Server db, renamed the
> table
> > > links removing the dbo_ from each and tested and all seems ok - no
> > problems
> > > yet.
> > >
> > > The security that I have is really to stop basic users from "playing"
> with
> > a
> > > registration table but I have no problems with an administrator having
> > > access to the tables. The only I have now is:
> > > 1. What am I missing???????
> > > 2. How do I include & package the SQL backend (I am using the PDW) -
> > perhaps
> > > this is where the 6 months of reading will come into it.
> > > 3. I have to figure how to allow the user that when starting the FE to
> > > select either "link to mdb" or "link to SQL Server" and the code to
> handle
> > > the linking process.
> > >
> > > Thanks again!
> > > -- 
> > > Regards
> > > Ian
> > > -
> > > "Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
> > > news:%23iZPbjr8DHA.2672@TK2MSFTNGP10.phx.gbl...
> > > > I agree.
> > > > Don't take this on unless you have time to do it right.
> > > > It sounds like a disaster wiating to happen right now.
> > > >
> > > > I suggest you hire someone with SQL Server experience to do the
> > conversion
> > > > and design correctly.
> > > > (Not me.)
> > > > -- 
> > > > Joe Fallon
> > > > Access MVP
> > > >
> > > >
> > > >
> > > > "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
message
> > > > news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> > > > > 0) I would not accept this project as 'urgent' if you have
> > > > > never done it before.
> > > > >
> > > > > 1) RWOP doesn't work with SQL tables. You will have to
> > > > > rethink your security model and copy protection.
> > > > >
> > > > > 2) Rename the Usys tables. You can rename the table links as
> > > > > Usys... and connect them to the new SQL Server tables.
> > > > >
> > > > > 3) You can use on FE for both Jet and SQL Server BE: you
> > > > > can attach linked tables to either Jet or SQL Server.
> > > > >
> > > > > 4) If you have never done this before, at least look at
> > > > > the SQL upsizer tool from SSW
http://www.ssw.com.au/ssw/UpsizingPRO/
> > > > >
> > > > > 5) If you add data to subforms you will have to modify
> > > > > your data entry process.  If you use complex transactions
> > > > > they won't work. If you use A97 you will have trouble
> > > > > working out how to connect to SS2000.
> > > > >
> > > > > (david)
> > > > >
> > > > >
> > > > > "Ian Baker" <ian@companyabc.com> wrote in message
> > > > > news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > > > > > We have a client who has an urgent need for one of our
"packaged"
> > apps
> > > > to
> > > > > be
> > > > > > used with full SQL Server (not just MSDE) and not Jet and as we
> know
> > > > > nothing
> > > > > > in this area we have some questions and any help is appreciated:
> > > > > > 1. Our app uses Access Security with an mdw file for development
> > which
> > > > is
> > > > > > not distributed as the app is accessed by the user's own
> system.mdw
> > > file
> > > > > as
> > > > > > we have a couple of tables blocked (i.e. hold registration info
> etc)
> > > and
> > > > > > only accessed by RWOP queries in the FE. Can this still be done
> with
> > > > SQL?
> > > > > > 2. Some of our tables start with "Usys" and it seems the wizard
> will
> > > not
> > > > > > acknowledge them. What is the best way to address this?
> > > > > > 3. Would we have to have different FE's i.e. one for a jet back
> end
> > > and
> > > > > one
> > > > > > for a SQL back end or can one FE be used with both and if so how
> > could
> > > > we
> > > > > do
> > > > > > it?
> > > > > > 4. For us to start reading up on all of this would take 6 months
> and
> > > we
> > > > > > don't have that luxury so is there any "summary" documents or
> sites
> > > that
> > > > > > would help us?
> > > > > > 5. Any other tips, suggestions, help etc that you could advise
> that
> > we
> > > > > > should be mindful of?
> > > > > >
> > > > > > Thanks for any advice you may be able to give
> > > > > >
> > > > > > Regards
> > > > > > Ian Baker
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
david
2/16/2004 2:59:05 AM
SQL Server database files are .mdf not .dbf. One way to distribute it is to
doa backup and restore.
The best way is to write scripts to build all objects. (EM can do some of
this work, but its output should be reviewed and edited.)

---------------------------------------------------------------------------
See http://www.mvps.org/access/tables/tbl0009.htm
for code to re-link Jet tables.
---------------------------------------------------------------------------

I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
  MsgBox "You must supply a DSN in order to link tables."
  Exit Sub
Else
  strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
  Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
  tdfAccess.Connect = dbsODBC.Connect
  tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
  dbs.TableDefs.Append tdfAccess
  rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
    SysCmd acSysCmdClearStatus
    Exit Sub

Err_LinkSQLServerTables:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

    Dim dbs As Database, tdf As TableDef, I As Integer
    Set dbs = CurrentDb
    For I = dbs.TableDefs.Count - 1 To 0 Step -1
      Set tdf = dbs.TableDefs(I)
      If (tdf.Attributes And dbAttachedODBC) Then
        dbs.TableDefs.Delete (tdf.Name)
      End If
    Next I

    dbs.Close
    Set dbs = Nothing

Exit_DeleteODBCTableNames:
    Exit Sub

Err_DeleteODBCTableNames:
    MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
    Resume Exit_DeleteODBCTableNames

End Sub

-- 
Joe Fallon
Access MVP



"Ian Baker" <ian@companyabc.com> wrote in message
news:OLWbiUC9DHA.2576@TK2MSFTNGP11.phx.gbl...
> Hi David
> "bog standard applications" - ?
> The application is called Jackaroo IT with a 30 day trial available for
> download at http://jackaroo.net.au and contains 60 tables, 300 queries
(both
> hard and SQL code), 60 forms (inc 22 subs), 18,000 lines of code and 2
> ActiveX controls. We have clients using it with 26 concurrent users using
> Jet but the new client wants to put a "light version" on all employee
> desktops so the old Jet just won't take it.
>
> -- 
> Regards
> Ian
> -
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:Om27%23C58DHA.1632@TK2MSFTNGP12.phx.gbl...
> > > 1. What am I missing???????
> >
> > The system is designed for bog standard applications (apart
> > from data problems and the autonumber problem with subforms).
> > If you have a bog standard application you may have escaped
> > with no problems at all.
> >
> > > 2. How do I include & package the SQL backend
> >
> > Any client that is using SQL Server will have a DBA that
> > will want to be part of the process.  You can distribute
> > a SQL Server database as a .dbf file, and the DBA can
> > load it into SQL Server and set up the user permissions.
> >
> > > 3. I have to figure how to allow the user that when starting the FE
> > > select either "link to mdb" or "link to SQL Server" and the code to
> >
> > A radio button on your re-link form.......  Look at the connect
> > properties of one of your upsized linked tables to see what the
> > new connect property should look like.
> >
> > (david)
> >
> >
> >
> > "Ian Baker" <ian@companyabc.com> wrote in message
> > news:%23wk%23P%2308DHA.1632@TK2MSFTNGP12.phx.gbl...
> > > Thanks Guys
> > > Ok, I must be missing something huge here as I just installed SQL
Server
> > > (Dev), opened my BE using my Developer.mdw, upsized all tables to SQL
> > Server
> > > (using A2003 although the app is released in A2k & A2k2) accepting all
> the
> > > defaults - everything ok. Open my FE, deleted all the jet table links,
> > > created new table links using ODBC to the SQL Server db, renamed the
> table
> > > links removing the dbo_ from each and tested and all seems ok - no
> > problems
> > > yet.
> > >
> > > The security that I have is really to stop basic users from "playing"
> with
> > a
> > > registration table but I have no problems with an administrator having
> > > access to the tables. The only I have now is:
> > > 1. What am I missing???????
> > > 2. How do I include & package the SQL backend (I am using the PDW) -
> > perhaps
> > > this is where the 6 months of reading will come into it.
> > > 3. I have to figure how to allow the user that when starting the FE to
> > > select either "link to mdb" or "link to SQL Server" and the code to
> handle
> > > the linking process.
> > >
> > > Thanks again!
> > > -- 
> > > Regards
> > > Ian
> > > -
> > > "Joe Fallon" <jfallon1@nospamtwcny.rr.com> wrote in message
> > > news:%23iZPbjr8DHA.2672@TK2MSFTNGP10.phx.gbl...
> > > > I agree.
> > > > Don't take this on unless you have time to do it right.
> > > > It sounds like a disaster wiating to happen right now.
> > > >
> > > > I suggest you hire someone with SQL Server experience to do the
> > conversion
> > > > and design correctly.
> > > > (Not me.)
> > > > -- 
> > > > Joe Fallon
> > > > Access MVP
> > > >
> > > >
> > > >
> > > > "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
message
> > > > news:O0ylOcg8DHA.2332@TK2MSFTNGP10.phx.gbl...
> > > > > 0) I would not accept this project as 'urgent' if you have
> > > > > never done it before.
> > > > >
> > > > > 1) RWOP doesn't work with SQL tables. You will have to
> > > > > rethink your security model and copy protection.
> > > > >
> > > > > 2) Rename the Usys tables. You can rename the table links as
> > > > > Usys... and connect them to the new SQL Server tables.
> > > > >
> > > > > 3) You can use on FE for both Jet and SQL Server BE: you
> > > > > can attach linked tables to either Jet or SQL Server.
> > > > >
> > > > > 4) If you have never done this before, at least look at
> > > > > the SQL upsizer tool from SSW
http://www.ssw.com.au/ssw/UpsizingPRO/
> > > > >
> > > > > 5) If you add data to subforms you will have to modify
> > > > > your data entry process.  If you use complex transactions
> > > > > they won't work. If you use A97 you will have trouble
> > > > > working out how to connect to SS2000.
> > > > >
> > > > > (david)
> > > > >
> > > > >
> > > > > "Ian Baker" <ian@companyabc.com> wrote in message
> > > > > news:e1yHfxf8DHA.1112@tk2msftngp13.phx.gbl...
> > > > > > We have a client who has an urgent need for one of our
"packaged"
> > apps
> > > > to
> > > > > be
> > > > > > used with full SQL Server (not just MSDE) and not Jet and as we
> know
> > > > > nothing
> > > > > > in this area we have some questions and any help is appreciated:
> > > > > > 1. Our app uses Access Security with an mdw file for development
> > which
> > > > is
> > > > > > not distributed as the app is accessed by the user's own
> system.mdw
> > > file
> > > > > as
> > > > > > we have a couple of tables blocked (i.e. hold registration info
> etc)
> > > and
> > > > > > only accessed by RWOP queries in the FE. Can this still be done
> with
> > > > SQL?
> > > > > > 2. Some of our tables start with "Usys" and it seems the wizard
> will
> > > not
> > > > > > acknowledge them. What is the best way to address this?
> > > > > > 3. Would we have to have different FE's i.e. one for a jet back
> end
> > > and
> > > > > one
> > > > > > for a SQL back end or can one FE be used with both and if so how
> > could
> > > > we
> > > > > do
> > > > > > it?
> > > > > > 4. For us to start reading up on all of this would take 6 months
> and
> > > we
> > > > > > don't have that luxury so is there any "summary" documents or
> sites
> > > that
> > > > > > would help us?
> > > > > > 5. Any other tips, suggestions, help etc that you could advise
> that
> > we
> > > > > > should be mindful of?
> > > > > >
> > > > > > Thanks for any advice you may be able to give
> > > > > >
> > > > > > Regards
> > > > > > Ian Baker
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
Joe
2/16/2004 11:55:17 PM
Reply:

Similar Artilces:

Outlook Web Access AutoSignature
I would like to know if it's possible to have autosignature in Outlook Web Access, and if so how to implement it. If anyone can help, it would be greatly apreciated. Thank You. It depends on the version of Exchange - Outlook Web Access is a function of Exchange, not Outlook. Exchange 2003 does this natively, previous versions may have a third party add-in that will allow users to do this. For better information, post in an exchange group such as microsoft.public.exchange.admin. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due t...

Restricting access to note attachments
Hi all, is there a way to restrict access to attachments on notes to specified users. I know we can, using the Security Roles, define who can and can't read/ write the notes fields but we would like our users to be able to view the note, but not open the attachment(s) unless they are the owner of that note (or at least in the business unit, etc.). I know this could be done for Word/ Excel files in Office (Password protection) but are looking for something more encompassing for all attachments inside CRM. Thanks, -- Matt Pring On Oct 21, 3:54=A0pm, Matt Pring <MattPr...@discus...

RPC over HTTPS only + Encrypt data between Outlook and Exchange server Option
I need to clarify the difference between having only RPC over HTTPS setup and working fine.... and adding the option in outlook 2003 to Encrypt data between Outlook and Exchange server Option what i need to know is: 1) what is gained in adding this option? 2) without adding this option what is the security issue? ...

TWO CRM SERVERS 02-03-06
Is it possible to install two CRM Servers in the same domain using a single Exchange Server? It will work four outbound email, but even with the v3.0 router changes, I do not know if it will work for inbound email. Probably need to check with support on this for sure. -- Matt Parks MVP - Microsoft CRM "Irshard" <Irshard@discussions.microsoft.com> wrote in message news:C18EF5EA-F47C-44CC-9B18-D2799DAF3ED4@microsoft.com... Is it possible to install two CRM Servers in the same domain using a single Exchange Server? yes it is. NOTE: This is already assuming you have two...

ADO/SQL Server: datetime column appears to be "1901-1-1 00:00:00" after insertion
Hi. I'm using ADO connection and recordset to insert records from Table1 to table2. The database is SQL Server 2005 and the connection is established before the following the code snippet with "csvSQL". The code works without any error but the result is not what i want. The issue is that when aTIME1 is "" and is inserted to table2, TIME1 of Table2 becomes "1901-1-1 00:00:00". FYI, TIME1 is of type datetime and allow null. What i expect is when aTIME1 is "" and inserted to Table2, TIME1 should be null. Please help. Thanks. Set csvR...

using paid servers with Outlook Express.
I've been using news.eternal-september.org, a free text server with Outlook Express. Does giganews work with Outlook Express? Can Outlook Express deal with binary newsgroups? Should I use a different news program like Forte Agent? I have a very old version of Forte Agent, but I like having my newsgroups right there by my email. Oops. I should have posted to microsoft.public.outlookexpress ...

Access 2003 and Access 2007 Synchronization
I'm setting up a new business relationship with a company in Portugal. They use Access 2003. I have just bought MS Office Professional 2007 (with Access 2007). Our plan is that they will send, once a month or so, their Access db then we will update and add to that db and then return it after another month or so. There probably won't be a lot of updates on our end so the monthly cycle should work. After they synchronize, they will send a new version of the db and we will start over. My goal is to keep everything as simple as possible partially because I don't know how skillf...

Excel Row to Access Columns
Hi, I have a single row in Excel with a lot of data, what I need to do is move that to a column in Access. I need some way to automate that. I know the easiest way to do that would to just transpose it in excel and then import in access.But I cant do that as I just get that excel file with one row and I need some way to automate the process in Access. thanks m -- mk389 ------------------------------------------------------------------------ mk389's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15272 View this thread: http://www.excelforum.com/showthread.php?...

Help displaying a random record in a form--Access newbie
Hello, I am new to Access, so if anyone can help me, please assume I know absolutely nothing! I currently have a table with two columns, ID and CHORE. In each row, the ID number corresponds to a chore that I have entered. I would like to create a form with a button and a text field such that when I press the button, a random chore is displayed in the text field (supposed to make chores more "fun" for my son). There is no need to remove this record from future clicks (the same record can come up over and over without any problem for me) Can anyone explain to me (in pai...

Access
Hi My form takes a few moments to load up as it is fairly substantial SQL statement is there a way to get something to display a graphic or 'loading...' message to appear why it calculate the contents? Steve ...

Moving contacts to Exchange 2000 Enterprise Server
Does anyone know an easy way to move my smtp addresses from CCmail 8.2 address book (about 400 of them) over to contacts in Active directory. I am running Windows 2000 Server and Exchange 2000 Enterprise Server. Cheers Crazy cc:Mail's export command with the /DIRECTORY switch can export them in an quasi - ldif format which could be massaged and used with ldifde to import. cc:Mail's Import/Export users guide will help on the export start with 237677 Using LDIFDE to import and export directory objects to Active Directory http://support.microsoft.com/default.aspx?scid=kb;EN-US;...

access mail on WEB
Is there a way i can check emails from exchanger server 5.5 from an internet browser. I have Exchange 5.5 in win NT 4.0. Thanks! Yes - install OWA (Outlook Web Access) and open up the needed ports in your firewall (80 for http; better to use SSL and port 443 instead). disk0nek wrote: > Is there a way i can check emails from exchanger server > 5.5 from an internet browser. > > I have Exchange 5.5 in win NT 4.0. > > Thanks! ...

access or mount mirrored hard disks
Hi, is it possible to access a mirrored harddrive? or mount the mirrored volume by using api or mfc commands? Thank you in advance Wimpi ...

SMTP server #2
I have a basic question on SMTP emails. If I point to a SMTP server that is different than my designated email host address, who actually sends out the emails, my designated email server or the one I pointed to? To help clarify we have our email and web hosted by a 3rd party Company "A", and my ISP is with Company "B", I have been having problems with duplicate emails being sent to several clients. So I pointed my outbout emails to a different SMTP server (Company B's smtp server) in an attempt to see if the problem goes away. So I am check to see if my...

Access Database In Great Plains
I was wondering if anyone knew how to have the databases that are stored in access transfered over to great plains. I'm current running version 8.0 any help would be greatful. thanks Search Customer Source for integration Manager. MS Access databases can be used as a data source. Vincev "jake" wrote: > I was wondering if anyone knew how to have the databases that are > stored in access transfered over to great plains. I'm current running > version 8.0 any help would be greatful. thanks > > Hi Jake, Is this something you need to do regularly or is it ...

CRM v4 Accessibility Improvements
Hi all: Does anyone know if any improvements have been med to CRM v4 in the area of accessibility for handicapped users? One of my customers has a user who is blind and they need to make the software accessible to him. Thanks in advance for any and all help. John. Hi John, We have added following Accessibility features in CRM 4.0 : 1) High Contrast Mode (Tools --> Options --> General Tab --> Enable high contrast settings) 2) All the Form input elements have <LABEL FOR> to aid in screen readers. 3) All the Images have a ALT tag (for non text elements) etc. Thanks! Ma...

Upsizing Access to SQL Server 2000
Hello, I am new to SQL Server. I am trying to use the Database upsizing wizard to upsize a Access 2000 database into SQL Server 2000. They are both on the same machine (running Win 03 Server). I cannot connect to the SQL Server. Error as below: Connection failed: SQLState: '01000' SQL Server Error: 14 [Microsoft][ODBC SQL Server Driver][Shared Memory] ConnectionOpen(Invalid Instance()). SQLState: '08001' SQL Server Error: 14 [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection. ...

Public Folders on 2nd server through OWA #2
HI I'm reposting it with new information. A client is running two exchange 2000 SP3 servers. One for mailboxes/outlook web access and 2nd sever dedicated for public folders. None of the servers is designated as front end server. Users can access mail through OWA using http://exchange.domain.com fine but when they tries to access public folders on the second server the browser points to http://server2.domain.com/public/foldername which is not accessible from outside. 2nd exchange server hosting pubic folders does have IIS installed and within LAN i can acess public folders on second se...

Outlook Web Access #19
We have have OWA at my work and I would like to configure Outlook 2003 to synchronize with my desktop at work, is this possible Thanks B ...

Have a user account accessible to multiple users
I have an issue where there is a user account that has multiple alias emails.(reservations@domain.com, info@domain.com, CustomerService@domain.com) I have an exchange 2003 server. My issue is that i need to have muliple users access these emails so they are never missed. What is the best way to accomplish this? I was thinking a public folder. But i want to know the best way to do this? On Sat, 12 Jun 2010 21:37:04 -0500, "Dan V" <dvalenti54athotmail.com> wrote: >I have an issue where there is a user account that has multiple alias >emails.(reservati...

CRM and SQL Log shipping
Hi Can you please advise on the following configuration please? 1x Web server for CRM front end 1x SQL server for CRM Db 1x SQL Standby server If we configure SQL log shipping or SQL Replication from live server to standby server, how do we switch CRM front end (web) to connect to the Standby server if live server fails over (without renaming server)? Your help will be appreciated -- Regards James[MVP for Great Plains] Visit MBS Blog Central http://mbscentral.blogs.com This can be done via Deployment Manager. I'd make sure to test this out thogh to ensure it works as desi...

outgoing email server
Our CRM was setup to use a SMTP virtual server in IIS on our CRM server. We also have an incoming mail server (exchange) in the same domain. Mail delivered through the CRM SMTP is not always delivered and returned to the user as undeliverable. If we send to the same email address using outlook (exchange server) to send out then the mail is delivered. Do I need to change the CRM server so it relays through the exchange server? I found a KB article referencing reg keys for changing the SMTP server but these keys don't exist in the registry. What is best way to setup? If keys do no...

Going offline without write access to registry
I suggest that POS doesn't go offline if it can't write into registry, displaying a message box instead telling that it couldn't access the registry. What happens if the POS machine goes offline without having access to edit the registry? This typically happen when the user is a normal user (not a power user) 1. The POS application will try to connect to the server main database within the pre-configured ‘Connect Timeout’ in the RMS Administrator (Typically 15 seconds) 2. The application will then try to connect to the pre-defined offline database (which is usually stored loc...

Access 2000 problem if opened with Access 2007
Dear all, I'm running Access 2000 MDE in Access 2007. I find several header-details form (such as invoice transaction), the details form is displayed as white box without any record displayed. Is there any solution for this matter ? Thanks. Chlaris. you need to move to Access Data Projects if you want a reliable, predictable environment "Chlaris" <chlarrissa_nospam@yahoo.com> wrote in message news:ON0rS1NsHHA.3884@TK2MSFTNGP04.phx.gbl... > Dear all, > > I'm running Access 2000 MDE in Access 2007. I find several header-details > form (such as inv...

Hacked SQL Dbase
Hi All, I've got a customer who decided to hack the CRM dbase (despite me saying not to) and I'm trying to reverse the changes. I'm trying to work out how he populated one field from another, in SQL, by using an unsupported hack. He changed it so that a field from the Productbase populates a field on QuoteDetailbase. I've been told that all he did was bring up the table design view of quotedetailbase, select the field from the quotedetailbase table then in the grid pane just found the column and selected the productbase as the table. Only thing is that it doesn't appea...