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 link two datasets together ?

Saludos !
given two datasets:
---
dataset1: select * from pubs.dbo.titles
dataset2: select * from pubs.dbo.sales
---
My question now is, how can i establish a relation between this two datasets
? And where (data register, layout register) ?
Or only with Subreports ?
thanks for any suggestion, hint...
JackHi,
It isn't possiple to link two datasets together or use more than one dataset
on a control.
But what about one dataset which unions both sql queries? And then you can
use a filter on a control to get only data from a single table.
e.g. the dataset:
select *, 'Titles' as TableName from pubs.dbo.titles
UNION
select *, 'Sales' as TableName from pubs.dbo.sales
and as filter to get titles data:
=Fields!TableName.Value='Titles'
Be sure that both queries you use to union have the same column names, so
don't use the *.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> Saludos !
> given two datasets:
> ---
> dataset1: select * from pubs.dbo.titles
> dataset2: select * from pubs.dbo.sales
> ---
> My question now is, how can i establish a relation between this two datasets
> ? And where (data register, layout register) ?
> Or only with Subreports ?
> thanks for any suggestion, hint...
> Jack
>
>|||Thanks Jan, but that is not exactly touching my issue: "titles" and "sales"
are different tables. In my opinion a union is isn't a proper way to do
this. Anyway...
My central idea behind my question is: If I could solve this problem, I
would be more flexible in using data for several reports. For example, I 've
got one special view for clients (supposing there are more tables to build
this view) , a second special view for sales and many more views for other
matters. In every report where I now have to use client-data, I can take the
same view. Otherwise I have to change every select statement for every
special report if anything changes...
>> Its a thing of strategy, do you see ?
jack
"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> schrieb im Newsbeitrag
news:BF29CB19-BD2C-4218-A9AE-F970A4D6E011@.microsoft.com...
> Hi,
> It isn't possiple to link two datasets together or use more than one
> dataset
> on a control.
> But what about one dataset which unions both sql queries? And then you can
> use a filter on a control to get only data from a single table.
> e.g. the dataset:
> select *, 'Titles' as TableName from pubs.dbo.titles
> UNION
> select *, 'Sales' as TableName from pubs.dbo.sales
> and as filter to get titles data:
> =Fields!TableName.Value='Titles'
> Be sure that both queries you use to union have the same column names, so
> don't use the *.
> --
> | Jan Pieter Posthuma
> --
>
> "Jack" wrote:
>> Saludos !
>> given two datasets:
>> ---
>> dataset1: select * from pubs.dbo.titles
>> dataset2: select * from pubs.dbo.sales
>> ---
>> My question now is, how can i establish a relation between this two
>> datasets
>> ? And where (data register, layout register) ?
>> Or only with Subreports ?
>> thanks for any suggestion, hint...
>> Jack
>>|||> My central idea behind my question is: If I could solve this problem, I
> would be more flexible in using data for several reports. For example, I 've
> got one special view for clients (supposing there are more tables to build
> this view) , a second special view for sales and many more views for other
> matters. In every report where I now have to use client-data, I can take the
> same view. Otherwise I have to change every select statement for every
> special report if anything changes...
Not sure this answers your question but...
Use a stored procedure to provide your data, then this stored procedure
can be re-used for all reports. And only has to be edited once for all
reports.
sarah|||You're right. thats the way we all doing it for years. But now for several
reasons we would like to use views instead of procs... so our customers are
able to easily create their own reports and links between their required
tables. without any knowledge of the underlying structure.
Jack
<sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
>
>> My central idea behind my question is: If I could solve this problem, I
>> would be more flexible in using data for several reports. For example, I
>> 've
>> got one special view for clients (supposing there are more tables to
>> build
>> this view) , a second special view for sales and many more views for
>> other
>> matters. In every report where I now have to use client-data, I can take
>> the
>> same view. Otherwise I have to change every select statement for every
>> special report if anything changes...
> Not sure this answers your question but...
> Use a stored procedure to provide your data, then this stored procedure
> can be re-used for all reports. And only has to be edited once for all
> reports.
> sarah
>|||Hi,
First sorry for my late reply, but I must say your last reply explained your
needs perfectly.
What you can try is to create a datasource with integrated authentication
turned on. This would force SRS to use the users credentials to log in to the
database. In the database views (to which the users only have read rights)
you can create a user specified select statement by filtering with the SQL
function SYSTEM_USER (SUSER_SNAME).
Unfortunaly you can only use select statements in views. So if your database
structure can't cope with this kind of behaviour, you can only fall back to
stored procedures.
Hope this would help you further. Feel free to ask for more information.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> You're right. thats the way we all doing it for years. But now for several
> reasons we would like to use views instead of procs... so our customers are
> able to easily create their own reports and links between their required
> tables. without any knowledge of the underlying structure.
> Jack
> <sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
> news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
> >
> >
> >> My central idea behind my question is: If I could solve this problem, I
> >> would be more flexible in using data for several reports. For example, I
> >> 've
> >> got one special view for clients (supposing there are more tables to
> >> build
> >> this view) , a second special view for sales and many more views for
> >> other
> >> matters. In every report where I now have to use client-data, I can take
> >> the
> >> same view. Otherwise I have to change every select statement for every
> >> special report if anything changes...
> >
> > Not sure this answers your question but...
> > Use a stored procedure to provide your data, then this stored procedure
> > can be re-used for all reports. And only has to be edited once for all
> > reports.
> > sarah
> >
>
>

Is it possible to access data associated with a hyperlink in SSIS?

I need to be able to to extract the data that is returned by clicking a hyperlink. I click the link and it displays the data.

I know that SSIS has a web services task, and that appears to be a neat feature, but the link I am connecting to does not have a WSDL file or anything like that. So I think I cannot use this task.

Does anyone have a suggestion about how I might do this?

Thanks for your help,

Harold

What data that is returned by clicking a hyperlink?

What is the hyperlink hosted in?

What defines or restricts this data?

What do you expect to happen to this extracted data?

SSIS is a backend ETL tool, I cannot work out what you would expect it to do for you, in what I am guessing is some kind of web UI

|||

Hello Darren,

Thanks for responding to my post.

I want to read the data that are displayed when I click a hyperlink.

When the package is executed, it should go to the hyperlink (URL) and get the data it displays.

The data that are displayed are:

var imgRates = {"ThirtyFixed":"6.298","FifteenFixed":"5.926","ThirtyFixedJumbo":"6.339","FiveOneARM":"5.831"};

I want to read that string into my package. I will then parse and store it.

Do you know if this can be done?

Thanks,

Harold

|||Your data looks to be a line of code. It doesn;t look very big, and not really what i woudl expct for an ETL source. Just write this in your programming language of choice, forget about SSIS.|||Here is some code that will go to a URL that you provide and read the content into a string.

Code Snippet

Dim webRequest As System.Net.WebRequest = System.Net.HttpWebRequest.Create(Dts.Variables("Url").Value.ToString())
Dim webResponse As System.Net.WebResponse = webRequest.GetResponse()
Dim stream As System.IO.Stream = webResponse.GetResponseStream()
Dim streamReader As New System.IO.StreamReader(stream)
Dim content As String = streamReader.ReadToEnd()

|||

Hello Jay,

The code snippet was just what I needed!

Thanks for your help, and for the others who took time to repond.

Harold

Is BOL incomplete or incorrect? YOU can make it better!

If you think that the BOL page you're reading has incorrect or incomplete information, you can make it better by using the Send Feedback link that's available on every page.

It's easy for the SQL Server documentation team to miss documentation issues buried in newsgroup and forum postings. But when you use the Send Feedback link, an automated process creates a bug in our database and assigns it directly to the owner of the individual topic on which you've commented. Now there's no way I can miss it. And my boss sees it too.

Please help us to make SQL Server Books Online better by using the Send Feedback link whenever appropriate!

Thanks,

-Doug

SQL Server Integration Services documentation team

P.S. Don't use the BOL feedback mechanism to send product feature requests, product gripes, or product bug reports...they won't reach their destination. It's intended exclusively for issues with BOL content.

I think it's already pretty good for a beta, but I will remember your suggestion.

Keep up the good work!