A
Andy
Hi ,
I am pretty new to Access; I am having difficulty in deciding on a course of
action.
My issues are these:
I need to create a workflow app using Access, our current accounting and
inventory control system runs off SQL ( i.e. MSDE) .The inventory control is
in fact a third party product.
I need to connect to this database in order to pull tables that contain
primary data for my Access app. (like the orders table, stock tables etc).
Currently I am using ODBC to do this. We have about 8 users in our workgroup
, I intend to split the BE and FE , making an MDE of the FE , package it
using Access Developers extensions and distribute run time versions to all
the users.( save on having to purchase licenses for all the users!)
My fear is that using ODBC will slow the workflow app down terribly, because
the Access app will be querying the tables in SQL extensively. Is there
another manner to pull these tables into my Access app?
(I have been thinking about going the Access project route i.e. using SQL as
the back end) My concern is that the MSDE provided by the third party
licensor has been modified in such a way that it will only allow 5 concurrent
connections. Considering this option these are the questions:
• Is it possible to load 2 instances of MSDE on one machine (i.e. the 3rd
party MSDE, and my own) that will feed different users?
• If yes, is this legal?
• Further , can I use the 2nd instance of MSDE to pull tables from the
primary database?( replication of sorts)
• If so how?
If I have to go the ODBC route, I have picked up a problem in making the FE
an MDE file , I get an error message along the lines that there are issues in
compiling large databases , only 2048 table id’s can be created , ( I don’t
have any where near 2000 objects in the database)
• What am I doing wrong?
• Is it even possible to make an MDE file when I have ODBC connections (
i.e. linked tables from SQL)
Are these the only options (do I need to get up to scratch on ADO)?
I know this is a bit convoluted, but any help will be greatly appreciated.
Regards
I am pretty new to Access; I am having difficulty in deciding on a course of
action.
My issues are these:
I need to create a workflow app using Access, our current accounting and
inventory control system runs off SQL ( i.e. MSDE) .The inventory control is
in fact a third party product.
I need to connect to this database in order to pull tables that contain
primary data for my Access app. (like the orders table, stock tables etc).
Currently I am using ODBC to do this. We have about 8 users in our workgroup
, I intend to split the BE and FE , making an MDE of the FE , package it
using Access Developers extensions and distribute run time versions to all
the users.( save on having to purchase licenses for all the users!)
My fear is that using ODBC will slow the workflow app down terribly, because
the Access app will be querying the tables in SQL extensively. Is there
another manner to pull these tables into my Access app?
(I have been thinking about going the Access project route i.e. using SQL as
the back end) My concern is that the MSDE provided by the third party
licensor has been modified in such a way that it will only allow 5 concurrent
connections. Considering this option these are the questions:
• Is it possible to load 2 instances of MSDE on one machine (i.e. the 3rd
party MSDE, and my own) that will feed different users?
• If yes, is this legal?
• Further , can I use the 2nd instance of MSDE to pull tables from the
primary database?( replication of sorts)
• If so how?
If I have to go the ODBC route, I have picked up a problem in making the FE
an MDE file , I get an error message along the lines that there are issues in
compiling large databases , only 2048 table id’s can be created , ( I don’t
have any where near 2000 objects in the database)
• What am I doing wrong?
• Is it even possible to make an MDE file when I have ODBC connections (
i.e. linked tables from SQL)
Are these the only options (do I need to get up to scratch on ADO)?
I know this is a bit convoluted, but any help will be greatly appreciated.
Regards