Is it possible to link via ADODB from an Access 2K .mdb file?

Hi all,
I am a newbie to SQL server and I am trying to link via ADODB from an Access
2000 .mdb file in Visual Basic to SQL server but I receive an error during
compilation at the "Dim rs As ADODB.Recordset" statement already.
It works if I do the same from an Access project file.
I assume this is not possible and I need to connect via DAO.
Does this also mean that I do not have the option to lock records at all if
I work
with a .mdb file?
Please help - I am puzzled.
Thanks.
Oliver
Let me give this a try, assuming I understand your scenario correctly.
You have an Access .mdb front-end that you wish to link
programmatically to a SQL Server database. If that is correct, then
you can create the link using a DAO.TableDef, not a recordset. You set
the properties of the TableDef, which include the connection string,
name, etc. The linked table is a Jet object, and DAO is always the
best choice when working with Jet objects. If you wish to create a
recordset based on SQL Server data, then use an ADO recordset. To
summarize: Jet=DAO, SQL Server=ADO.
--Mary
On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:

>Hi all,
>I am a newbie to SQL server and I am trying to link via ADODB from an Access
>2000 .mdb file in Visual Basic to SQL server but I receive an error during
>compilation at the "Dim rs As ADODB.Recordset" statement already.
>It works if I do the same from an Access project file.
>I assume this is not possible and I need to connect via DAO.
>Does this also mean that I do not have the option to lock records at all if
>I work
>with a .mdb file?
>Please help - I am puzzled.
>Thanks.
>Oliver
|||Thanks Mary, but is it possible to lock records on SQL server with DAO?
If not I will have to convert my .mdb into a project as I think ADO is only
possible if the Access client application is a project file (.adp extension).
I do not like to do this because I then have about 850 Queries that do not
work anymore! I would then need to convert all queries into stored procedures
and views - is that correct or is there a way around it?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Let me give this a try, assuming I understand your scenario correctly.
> You have an Access .mdb front-end that you wish to link
> programmatically to a SQL Server database. If that is correct, then
> you can create the link using a DAO.TableDef, not a recordset. You set
> the properties of the TableDef, which include the connection string,
> name, etc. The linked table is a Jet object, and DAO is always the
> best choice when working with Jet objects. If you wish to create a
> recordset based on SQL Server data, then use an ADO recordset. To
> summarize: Jet=DAO, SQL Server=ADO.
> --Mary
> On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:
>
|||Locking records on SQL Server from any client is a BIG mistake. SQLS
is very efficient at holding locks for the minimum amount of time
required. Locking records on the client for long periods of time
causes blocking and deadlocks (scenario--user runs code that locks
records, goes to lunch, leaving records locked). Another process
cannot even SEE the data if you are using the default READ COMMITTED
isolation level (see SQL Books Online for more info).
You should use other methods to control concurrency violations, such
as designing table schema to partition tables so that users don't
access the same record at the same time, using timestamps to detect
concurrency problems, or creating a column in the table that
increments each time a record is updated (you check this value in your
code prior to updating and increment during the update). If you care
about efficiency and network traffic, don't use DAO. Using ADPs will
provide no benefits in your situation--rewriting your DAO as ADO will
be less work. Also, don't use any kind of recordset to update data
unless you are trying to slow your application down. Use UPDATE
statements instead.
--Mary
On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Thanks Mary, but is it possible to lock records on SQL server with DAO?
>If not I will have to convert my .mdb into a project as I think ADO is only
>possible if the Access client application is a project file (.adp extension).
>I do not like to do this because I then have about 850 Queries that do not
>work anymore! I would then need to convert all queries into stored procedures
>and views - is that correct or is there a way around it?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Hi Mary, thanks for the tips.
I just thought that it is too much work to convert all the DAO code and all
of the 600 queries that did not convert with the upsizing wizard. The views
are mostly not updateable after upsizing - it seems I will have to rewrite
the whole system and I think Microsoft should have left it to us programmers
to decide if we want to rewrite it all by just allowing record locking in DAO
ODBC links. I spent a whole day yesterday trying out if DAO allows record
locks but it does not (they could at least have mentioned this in the help
system).
After having tried this out I think you are right - there is not other way
than to convert all code into ADO in one go. You mentioned that I should use
UPDATEs instead of recordset updates - do you mean I should use ADO commands
executed from visual basic or should I write update procedures on the server
and call those stored procedures from the visual basic?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Locking records on SQL Server from any client is a BIG mistake. SQLS
> is very efficient at holding locks for the minimum amount of time
> required. Locking records on the client for long periods of time
> causes blocking and deadlocks (scenario--user runs code that locks
> records, goes to lunch, leaving records locked). Another process
> cannot even SEE the data if you are using the default READ COMMITTED
> isolation level (see SQL Books Online for more info).
> You should use other methods to control concurrency violations, such
> as designing table schema to partition tables so that users don't
> access the same record at the same time, using timestamps to detect
> concurrency problems, or creating a column in the table that
> increments each time a record is updated (you check this value in your
> code prior to updating and increment during the update). If you care
> about efficiency and network traffic, don't use DAO. Using ADPs will
> provide no benefits in your situation--rewriting your DAO as ADO will
> be less work. Also, don't use any kind of recordset to update data
> unless you are trying to slow your application down. Use UPDATE
> statements instead.
> --Mary
> On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
>
|||I think the reason you may have had trouble discovering how DAO works
with SQL Server in the help files is that there is an assumption that
you will use it only with Jet. It is not intended to work with SQL
Server, so nobody thought to document it. However, you can still use
DAO to execute pass-through queries, which are quite efficient. You
can use existing QueryDef objects and set the .SQL property in DAO
code to a SQL statement or to execute a stored procedure. Or you can
create dynamic pass-through queries that are not persisted in the mdb.
The syntax you use in the .SQL property is T-SQL, not Access SQL. The
reason they are called pass-through queries is that the SQL is not
parsed by Access--it is sent directly to the server. You can also use
ADO commands to execute SQL statements or parameterized stored
procedures. HTH,
--Mary
On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Hi Mary, thanks for the tips.
>I just thought that it is too much work to convert all the DAO code and all
>of the 600 queries that did not convert with the upsizing wizard. The views
>are mostly not updateable after upsizing - it seems I will have to rewrite
>the whole system and I think Microsoft should have left it to us programmers
>to decide if we want to rewrite it all by just allowing record locking in DAO
>ODBC links. I spent a whole day yesterday trying out if DAO allows record
>locks but it does not (they could at least have mentioned this in the help
>system).
>After having tried this out I think you are right - there is not other way
>than to convert all code into ADO in one go. You mentioned that I should use
>UPDATEs instead of recordset updates - do you mean I should use ADO commands
>executed from visual basic or should I write update procedures on the server
>and call those stored procedures from the visual basic?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Thanks Mary, in the meantime I found a good link to an old documentation
about the use of ODBCDirect,
http://msdn.microsoft.com/archive/de...l/web/001.asp.
This gives me even the option of pessimistic record locking (I need this
sometimes). I already tried to convert everything into ADO but this is an
endless job with the amount of code and queries I have (I gave up!). Now I
can program new queries as stored procedures and views on the server but
still keep the old queries in Access functional. If a query is too slow I
just convert it as needed. This is a much better way of migration into SQL
server.
Oliver
"Mary Chipman [MSFT]" wrote:

> I think the reason you may have had trouble discovering how DAO works
> with SQL Server in the help files is that there is an assumption that
> you will use it only with Jet. It is not intended to work with SQL
> Server, so nobody thought to document it. However, you can still use
> DAO to execute pass-through queries, which are quite efficient. You
> can use existing QueryDef objects and set the .SQL property in DAO
> code to a SQL statement or to execute a stored procedure. Or you can
> create dynamic pass-through queries that are not persisted in the mdb.
> The syntax you use in the .SQL property is T-SQL, not Access SQL. The
> reason they are called pass-through queries is that the SQL is not
> parsed by Access--it is sent directly to the server. You can also use
> ADO commands to execute SQL statements or parameterized stored
> procedures. HTH,
> --Mary
> On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
>

Is it possible to have the .rds file in reports folder

Since i have 5 different projects all has the same kind of reports., but calling via 5 different sites all sites on same webserver.

The problem i have is the .rds file name is same in all 5 report projects and it is a shared datasource., now when i compile the report project it will not load or overwrite the .rds file since the name is same, for that reason i tried to have the .rds file in reports folder as a datasource not a shared datasource. but i tried to add it, after i create the datasource, it is automatically going into the shared datasource folder, how can i create a datasource not shared under reports folder.

Thank you very much for all your help and information.

Hello,

From the Project menu, select 'Properties'. In the TargetDataSourceFolder, type the path of where you want the data source to go. From Books Online:

TargetDataSourceFolder

Type the name of the destination folder for publishing the shared data sources that are contained within the project. This value is optional. If you do not specify a folder, the data source is published to the same folder as the report. If the folder does not exist on the report server, Report Designer creates the folder when the reports are published. If a folder is located within another folder, include the path to the folder, starting at the root, for example, Folder1/Folder2/Folder3.

Hope this helps.

Jarret

|||Thank you very much Jarret.....

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>sql

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Asked and answered in .programming. Please refrain from multiposting.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>|||Take a look at SET CONTEXT_INFO in the Books Online. That allows you to set
a value for the connection that is accessible anywhere in the session with
the CONTEXT_INFO function. Since the value is binary, you'll need to
convert as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

Is it possible pass to procedure open cursor from other procedure ??

Is it possible pass to procedure open cursor from other procedure ?
Any suggestions will be appreciated
Message posted via http://www.webservertalk.comYes, you can declare a cursor as an output a parameter of a stored
procedure. But it is most likely not the most efficient way to share data
between procedures. The alternatives are discussed by SQL Server MVP Erland
Sommarskog in the following article:
http://www.sommarskog.se/share_data.html
Jacco Schalkwijk
SQL Server MVP
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:bd838d17bc034f26a19b475812a8eeb7@.SQ
webservertalk.com...
> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com|||Yes, but it's probably not a good idea. Could you explain your
requirement. There's sure to be a better way.
David Portas
SQL Server MVP
--|||In my db i call to some procedure suppese MyProc from application and pass
some XML,
inside the MyProc i parse the xml, insert the data to temporary table and
now according to some information that i got from xml call to other
procedure suppose proc1 or
proc2 or ... and in one of these procedures i perform some operation on
data that exist in temporary table. Actually i solve this problem with
temporary table (#myTable) however it's not good idea to use it and i can't
to use it, i don't know what to do.
The problem is that according to data in XML i call to appropriate
procedure, i can parse the xml in Myproc sp and call to relevant nested
procedure
proc1 or proc2, ... and pass this XML again however i don't want to open
and
parse the XML twice
Message posted via http://www.webservertalk.com|||This doesn't make a lot of sense to me as a design for a process in SQL
Server. You should parse your XML once only in order to load it into
appropriate tables. TSQL provides a proc to do this:
sp_xml_removedocument. Then execute procs from the data in tables. That
way you should avoid lots of messy cursors, temp tables and procedural
code. XML is for data-interchange only - it's a lousy way to persist
data and move it around inside the database.
David Portas
SQL Server MVP
--|||CORRECTION: sp_xml_preparedocument is the name of the proc you want.
David Portas
SQL Server MVP
--|||my problem is that after i parse the xml i fill temporary table with it's
data and now i need to call to other procedure to which i want pass
temporary table, but it isn't good idea also pass an open cursor is not
good idea
Message posted via http://www.webservertalk.com|||So why create two separate SPs and why load the data into a temporary
table or a cursor? You seem to be looking for a solution to a problem
that wouldn't exist if you made a better design.
David Portas
SQL Server MVP
--|||because according to the data that i got from XML i call to appropriate
procedure
Message posted via http://www.webservertalk.com|||OK. So load the XML data into tables where it belongs. Then execute the
logic for BOTH procedures but add or modify the WHERE clauses in your
DML code such that the logic only executes as appropriate. Example
pseudo-code:
Instead of this:
IF x=1
EXEC usp_proc1
IF x=2
EXEC usp_proc2
Do this:
..
WHERE x = 1
..
WHERE x = 2
In other words, adopt the declarative, set-based approach rather than a
procedural approach.
David Portas
SQL Server MVP
--

Is it possible make reportins via Sql Server?

Hi there ..is it possible ?
I'm new in this matter...
Thanks in advanceSQL Server (the server itself), is a data engine and does not have a front
end... You will have to have some front-end software for even simple
reports...
Query Analyzer comes with sql server, you can do select and save/print the
results, but there is esentially no formatting capabilities.
Reporting Services, Crytstal reports, and other tools provide true reporting
capabilities, and of course you can write your own applications.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Xavi" <Xavi@.discussions.microsoft.com> wrote in message
news:155526BD-ED86-48B8-9E94-9325BD15999C@.microsoft.com...
> Hi there ..is it possible ?
> I'm new in this matter...
> Thanks in advance|||That's what reporting services is all about :-). You can get more info
here: http://www.microsoft.com/sql/reporting.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Xavi" <Xavi@.discussions.microsoft.com> wrote in message
news:155526BD-ED86-48B8-9E94-9325BD15999C@.microsoft.com...
> Hi there ..is it possible ?
> I'm new in this matter...
> Thanks in advance

Is it ok?

Here is what's happening to my database -
I have a table that shows about 40 dependencies in
via EM. Thereafter, when I add and delete certain columns
in the table, it shows about 8 dependencies. Even though
the columns I am adding/deleting don't have any reference
in dependencies.
In this instance is it ok to use the application or its
a matter of concern. How do I resolve this?
Thank you,
-LindaAre these dependencies other tables? Perhaps the referential integrity
constraints were defined that way?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Is data sent via DTS encrypted?

Hi all,

I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.

If I create a DTS package transferring data from site A, will that transfer be encrypted?

If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?

Thanks in advance.No, data isn't automagically encrypted when using DTS. You can use a secure (VPN) link that will encrypt the data. You can put the data into a file and encrypt the file (if you do, compress it first to improve security). You can even create an encryption/decryption DTS task (using VC) and make that part of your DTS toolbox.

-PatP|||Thanks Pat.

Not sure that secure VPN is an option to be honest I'm afraid.

The current favourite is actually to ftp an encrypted .csv, I just wondered if DTS could handle the encryption and make my life easier :-)

BTW, what is VC ( or am I just having a blonde moment? )|||VC is Visual C, part of Microsoft's Visual Studio.

The only way I know to add new tasks to DTS is to write them in Visual C. This allows all kinds of interesting things to happen!

Blonde moments are expected, at least now and then. Unfortunately, I don't have blonde hair (heck, I don't have much hair), so I have to attribute those moments to other things... I'm not quite to the point where I can comfortably attribute them to senior moments yet, although from an IT perspective I'm already somewhat older than dirt!

Yes, go ahead and create the CSV file, then use an Execute Process (effectively a command line) Task to handle the encryption.

-PatP|||Thanks for all the advice Pat. I'll investigate that process.

I too am quite far from blonde, but hey, we all have the moments :-)|||Unless I'm having a Blonde moment I'm almost certain DTS tasks can be written in C# and VBScript (the tools I've used) just to name a few.|||I only know how to create a new task that you can add to the DTS designer Task Panel using Visual C. You can certainly add steps to a DTS package using C# or VBScript, but that is a completely different thing in my opinion.

-PatP|||Why not just create a stored procedure?|||There are more ways to skin that cat than there are cats, but that is no reason to stop looking for new ways!

A script or stored procedue would be easy to implement. A custom task would be more efficient to use, since you could for instance just create a step that generated your data, then feed it to the task just like you do with the FTP task.

I did something like this to move data to our mainframe using the 7.0 EM a while ago, and the task was wildly popular with our power users. It just saved them a lot of monkeying around for a task that they did rather frequently.

-PatP|||I only know how to create a new task that you can add to the DTS designer Task Panel using Visual C. You can certainly add steps to a DTS package using C# or VBScript, but that is a completely different thing in my opinion.

-PatP

Ah.. yes. Your looking to make a new task for the DTS toolbar. I missed that. C# could probably handle it but I have doubts about scripting languages.

is ASP the only way for SQL remote access via internet

Hi.
I would like to access my database outside of my company. I read many
documents but they are all pertaining to accessing the database via
ASP or some form of web application. Is there no single windows or
linux application tht runs natively to access a remote SQL data base?
Any advise is appreciated. Thanks!!The only requirement for an application to access a remote SQL Server is
network connectivity. There are additional considerations with remote
client-server access, such as security, deployment complexity and
performance. These are reasons why a web-based architecture is most often
chosen for remote data access.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kackson" <kackson@.yahoo.com> wrote in message
news:90250227.0407050549.4b832321@.posting.google.c om...
> Hi.
> I would like to access my database outside of my company. I read many
> documents but they are all pertaining to accessing the database via
> ASP or some form of web application. Is there no single windows or
> linux application tht runs natively to access a remote SQL data base?
> Any advise is appreciated. Thanks!!|||I suppose you could set up a VPN to your server and use an access ADP
project on the local machine to connect

"kackson" <kackson@.yahoo.com> wrote in message
news:90250227.0407050549.4b832321@.posting.google.c om...
> Hi.
> I would like to access my database outside of my company. I read many
> documents but they are all pertaining to accessing the database via
> ASP or some form of web application. Is there no single windows or
> linux application tht runs natively to access a remote SQL data base?
> Any advise is appreciated. Thanks!!|||Hi.
What exactly is access ADP? If I setup a VPN, is this "access ADP" the
only best secure method? Are there alternatives that are secure and
proven to be the sort of industry standard method (the way that most
people do) where native applications (not via net applications) access
databases over internet? Any advise is really much appreciated.

"aaj" <a.b@.c.com> wrote in message news:<40ea74dd$0$9974$afc38c87@.news.easynet.co.uk>...
> I suppose you could set up a VPN to your server and use an access ADP
> project on the local machine to connect
>
> "kackson" <kackson@.yahoo.com> wrote in message
> news:90250227.0407050549.4b832321@.posting.google.c om...
> > Hi.
> > I would like to access my database outside of my company. I read many
> > documents but they are all pertaining to accessing the database via
> > ASP or some form of web application. Is there no single windows or
> > linux application tht runs natively to access a remote SQL data base?
> > Any advise is appreciated. Thanks!!|||> What exactly is access ADP?

An ADP is a Microsoft Access project file that uses SQL Server as the
underlying database engine.

> If I setup a VPN, is this "access ADP" the only best secure method?

An ADP is basically just a client application. It is the VPN connection
that provides the secure network connection needed for a client-server
application to use a remote database over the public internet.

Although you can run the application locally and access the remote database
over a VPN connection, another common method is to establish a remote
desktop connection over a VPN to an application server and run the app
there. Like a web app, this can reduce network overhead and facilitate
deployment.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kackson" <kackson@.yahoo.com> wrote in message
news:90250227.0407080205.31978dc3@.posting.google.c om...
> Hi.
> What exactly is access ADP? If I setup a VPN, is this "access ADP" the
> only best secure method? Are there alternatives that are secure and
> proven to be the sort of industry standard method (the way that most
> people do) where native applications (not via net applications) access
> databases over internet? Any advise is really much appreciated.
>
> "aaj" <a.b@.c.com> wrote in message
news:<40ea74dd$0$9974$afc38c87@.news.easynet.co.uk>...
> > I suppose you could set up a VPN to your server and use an access ADP
> > project on the local machine to connect
> > "kackson" <kackson@.yahoo.com> wrote in message
> > news:90250227.0407050549.4b832321@.posting.google.c om...
> > > Hi.
> > > I would like to access my database outside of my company. I read many
> > > documents but they are all pertaining to accessing the database via
> > > ASP or some form of web application. Is there no single windows or
> > > linux application tht runs natively to access a remote SQL data base?
> > > Any advise is appreciated. Thanks!!|||As Dan says, an ADP is the Microsoft Access (I think Access 2000 onwards)
front end i.e. the forms etc, but it doesn't use the jet backend, instead it
connects to the SQL Server.

The VPN provides a local IP address on the remote machine that the ADP can
connect to and points it in the direction of the database. I imagine (but
I'm not certain) that any other from end could make a similar connection
e.g. enterprise manager

I suppose bot the VPN and Access are industry standard, but I'm not sure
this type of configuration would be. The only reason we did it like this was
just to see if we could.

I imagine (and I think is what Dan alludes to further down) that using
something like Citrix or terminal server to connect to whatever you use to
access your SQL Server may be a more sensible option.

hope this helps

Andy

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:jraHc.8856$R36.2433@.newsread2.news.pas.earthl ink.net...
> > What exactly is access ADP?
> An ADP is a Microsoft Access project file that uses SQL Server as the
> underlying database engine.
> > If I setup a VPN, is this "access ADP" the only best secure method?
> An ADP is basically just a client application. It is the VPN connection
> that provides the secure network connection needed for a client-server
> application to use a remote database over the public internet.
> Although you can run the application locally and access the remote
database
> over a VPN connection, another common method is to establish a remote
> desktop connection over a VPN to an application server and run the app
> there. Like a web app, this can reduce network overhead and facilitate
> deployment.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kackson" <kackson@.yahoo.com> wrote in message
> news:90250227.0407080205.31978dc3@.posting.google.c om...
> > Hi.
> > What exactly is access ADP? If I setup a VPN, is this "access ADP" the
> > only best secure method? Are there alternatives that are secure and
> > proven to be the sort of industry standard method (the way that most
> > people do) where native applications (not via net applications) access
> > databases over internet? Any advise is really much appreciated.
> > "aaj" <a.b@.c.com> wrote in message
> news:<40ea74dd$0$9974$afc38c87@.news.easynet.co.uk>...
> > > I suppose you could set up a VPN to your server and use an access ADP
> > > project on the local machine to connect
> > > > > "kackson" <kackson@.yahoo.com> wrote in message
> > > news:90250227.0407050549.4b832321@.posting.google.c om...
> > > > Hi.
> > > > I would like to access my database outside of my company. I read
many
> > > > documents but they are all pertaining to accessing the database via
> > > > ASP or some form of web application. Is there no single windows or
> > > > linux application tht runs natively to access a remote SQL data
base?
> > > > Any advise is appreciated. Thanks!!