Access DB via SqlDataSource....need result in DataSet

Hi all...I've set the DataSourceMode = SqlDataSourceMode.DataSet, and did a .Type return and found that it is actually returning a DataView. A component I am trying to avoid rewriting...requires a dataset that loops through the table, does some cool formatting to a datagrid and then rebinds.

Here's the code that I'm trying to send the dataset to...maybe there's just a couple of changes that could make it work with a DataView?

int i = 0;
string prevsub = "";
while (i <= ds.Tables[0].Rows.Count - 1)
{
DataRow dr = ds.Tables[0].Rows[i];
string sub = dr["SubHeading"].ToString();
if (sub != prevsub)
{
prevsub = sub;
DataRow newrow = ds.Tables[0].NewRow();
newrow["Title"] = "SubHeading";
newrow[columnName] = dr[columnName];
ds.Tables[0].Rows.InsertAt(newrow, i);
i++;
}
i++;
}

DataView is very close to the dataset object in this context.

my test code:

DataView dv = (DataView) SqlDataSource1.Select(DataSourceSelectArguments.Empty);

int i = 0;
string prevsub = "";
while (i <= dv.Table.Rows.Count - 1)
{
DataRow dr = dv.Table.Rows[i];
string sub = dr["mycol1"].ToString();
if (sub != prevsub)
{
prevsub = sub;
DataRow newrow = dv.Table.NewRow();
newrow["mycol1"] = "SubHeading";
newrow["mycol2"] = dr["mycol2"];
dv.Table.Rows.InsertAt(newrow, i);
i++;
}
i++;
}

Hope this helps.

|||Hi there. Thanks so much limno! That did the trick. I was hoping that the dv would be similar enough to be able to use this code. Great to know and use for the future. Much appreciated!

access and Sql

I changed the archive of a program in VB6 that used
Access 97 Database via DAO USING JET 3.5 passing it to SQL
server (sqloledb1 driver) and using ado 2.7 and with
surprise (The file structure and data is not grown) I find
now the program slower than before using access file' It
is possible? Sql server is slower to access the file than
access' or i make something wrong"davidex" <anonymous@.discussions.microsoft.com> wrote in message
news:318901c3b033$a560efc0$a601280a@.phx.gbl...
> I changed the archive of a program in VB6 that used
> Access 97 Database via DAO USING JET 3.5 passing it to SQL
> server (sqloledb1 driver) and using ado 2.7 and with
> surprise (The file structure and data is not grown) I find
> now the program slower than before using access file' It
> is possible? Sql server is slower to access the file than
> access' or i make something wrong
Don't multi-post. See thread in .programming.
David

Access 2007 linked tables (vs Access 2003)

We migrated a MS Access 2003 mdb into MS Access 2007. The mdb has linked tables to SQL Server via a DSN and utilizes a mdw file. In 2003, the username/password is "passed" to SQL Server, so the UID/PWD that is used for opening the mdb, is used in SQL Server.

Opening the same file in 2007 using the same mdw, gives a secondary login on SQL Server.

Is there a way to have MS Access 2007 pass the UID/PWD to SQL Server on linked tables, the same way that 2003 does?

Thanks!

I hope someone answers this. I would really like to see the answer...

Access 2000 to SQL Server 2000 Standard

Sorry for the crosspost!!
I need a way of replicating from an Access Database to an SQL Server via the
internet. It is Access 2000 to SQL Server 2000 Standard. I would like this
functionality to be fairly flexible (which is probably asking a lot) so it
could work with Access 2000 upwards, as I can see this being really useful
to several clients who have Access information. I have looked at the
replication manager but that appears to be discontinued and only available
in developer additions. You would think the information would be a little
easier to find. Can anyone point me to some websites, resources or books.
Regards, Chris.
Merge Replication with the Access Database as a Subscriber will allow you to
replicate from Access to SQL Server and vice versa.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eEjmSFSgEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Sorry for the crosspost!!
> I need a way of replicating from an Access Database to an SQL Server via
the
> internet. It is Access 2000 to SQL Server 2000 Standard. I would like this
> functionality to be fairly flexible (which is probably asking a lot) so it
> could work with Access 2000 upwards, as I can see this being really useful
> to several clients who have Access information. I have looked at the
> replication manager but that appears to be discontinued and only available
> in developer additions. You would think the information would be a little
> easier to find. Can anyone point me to some websites, resources or books.
> Regards, Chris.
>
|||Chris,
to follow from Hilary's post, if you are not familiar with merge
replication, the best resource is BOL. If you want a web setup guide, have a
look at this site:
http://www.mssqlcity.com/Articles/Re...MR/SetupMR.htm
HTH,
Paul Ibison
|||I need to have a button on an access form which pushes the access data from
an external network to our local sql server. It has to be done via the
internet and there will be a firewall etc. The merge replication stuff in
BOL doesn't address the internet delivery in enought detail for a beginner
like me. I was looking at Office XP developer with replication manager and
doing it Access XP to Access XP on one of our web server and then link the
table to SQL server. Any thoughts? Does your book cover Access? Replication
covers many of the problems of clients want addressed in terms of linking
their local information to online systems but many clients will not upgrade
to SQL Server so I am looking at either Access XP developer or some kind of
custom .net component which sends the infomation via an XML Dataset and
merges it with a dataset on our web server. At least the .net solution
bypasses any firewall problems but it is reinventing the wheel to a big
extent.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23C4hkETgEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Merge Replication with the Access Database as a Subscriber will allow you
to[vbcol=seagreen]
> replicate from Access to SQL Server and vice versa.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eEjmSFSgEHA.1972@.TK2MSFTNGP09.phx.gbl...
> the
this[vbcol=seagreen]
it[vbcol=seagreen]
useful[vbcol=seagreen]
available[vbcol=seagreen]
little[vbcol=seagreen]
books.
>
|||AFAIK you can't replicate to an access database over the internet. So my
book does not cover this I do discuss how to set up an access database as
a Subscriber though.
If you can convert your Access database to MSDE you could do this. You would
do this using the ActiveX controls. These controls have a
DistributorAddress/DistributorNetwork, PublisherAddress/PublisherNetwork
properties which will allow you to pull the Susbcription over the internet.
I also discuss this in the book.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eQ7CeuTgEHA.1196@.TK2MSFTNGP11.phx.gbl...
> I need to have a button on an access form which pushes the access data
from
> an external network to our local sql server. It has to be done via the
> internet and there will be a firewall etc. The merge replication stuff in
> BOL doesn't address the internet delivery in enought detail for a beginner
> like me. I was looking at Office XP developer with replication manager and
> doing it Access XP to Access XP on one of our web server and then link the
> table to SQL server. Any thoughts? Does your book cover Access?
Replication
> covers many of the problems of clients want addressed in terms of linking
> their local information to online systems but many clients will not
upgrade
> to SQL Server so I am looking at either Access XP developer or some kind
of[vbcol=seagreen]
> custom .net component which sends the infomation via an XML Dataset and
> merges it with a dataset on our web server. At least the .net solution
> bypasses any firewall problems but it is reinventing the wheel to a big
> extent.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23C4hkETgEHA.2916@.TK2MSFTNGP12.phx.gbl...
you[vbcol=seagreen]
> to
via[vbcol=seagreen]
> this
so
> it
> useful
> available
> little
> books.
>
|||Excellent article but any pointers how to trigger Access sitting on an
external network to push information into sql server (I forgot to mention
there is a firewall but we may be able to specify an IP and port which my IT
manager should allow). The article gets me 3/4 of the way there but how do I
point SQL Server at an Access Database sitting on an external LAN via the
internet.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eSD5leTgEHA.644@.tk2msftngp13.phx.gbl...
> Chris,
> to follow from Hilary's post, if you are not familiar with merge
> replication, the best resource is BOL. If you want a web setup guide, have
a
> look at this site:
> http://www.mssqlcity.com/Articles/Re...MR/SetupMR.htm
> HTH,
> Paul Ibison
>
|||Presumably they could keep their Access database, install MDSE and just set
up Access as a linked database. That would cause minimal disruption and no
cost. How much is your book?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23cHBixTgEHA.3348@.TK2MSFTNGP12.phx.gbl...
> AFAIK you can't replicate to an access database over the internet. So my
> book does not cover this I do discuss how to set up an access database
as
> a Subscriber though.
> If you can convert your Access database to MSDE you could do this. You
would
> do this using the ActiveX controls. These controls have a
> DistributorAddress/DistributorNetwork, PublisherAddress/PublisherNetwork
> properties which will allow you to pull the Susbcription over the
internet.[vbcol=seagreen]
> I also discuss this in the book.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eQ7CeuTgEHA.1196@.TK2MSFTNGP11.phx.gbl...
> from
in[vbcol=seagreen]
beginner[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
> Replication
linking[vbcol=seagreen]
> upgrade
> of
> you
> via
like
> so
>
|||Chris,
Not read the thread in detail, but if replicating Access XP/2000 to Access
XP/2000 is what you need then this does work over the internet using only a
standard IIS port. There's a Microsoft document 'Internet Synchronization
with the Microsoft Jet Database Engine: A Technical Overview', which is a
great help. I've had this working for about 2 years, and it does the job but
if you can you'll find SQL Server / MSDE replication more reliable and
easier to manage. You don't really need replication manager. I have some
code for our own 'replication scheduler' I'd be willing to share. Another
good site for information is www.trigeminal.com, where you can find another
'replication manager' program.
Tony.
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eEjmSFSgEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Sorry for the crosspost!!
> I need a way of replicating from an Access Database to an SQL Server via
the
> internet. It is Access 2000 to SQL Server 2000 Standard. I would like this
> functionality to be fairly flexible (which is probably asking a lot) so it
> could work with Access 2000 upwards, as I can see this being really useful
> to several clients who have Access information. I have looked at the
> replication manager but that appears to be discontinued and only available
> in developer additions. You would think the information would be a little
> easier to find. Can anyone point me to some websites, resources or books.
> Regards, Chris.
>
|||My understanding is that this allows you to replicate an Access Database to
another Access Database over the internet. but does not allow you to
replicate an Access Database to a SQL Server or vice versa over the
internet.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:cfj3gr$k2s$1$830fa17d@.news.demon.co.uk...
> Chris,
> Not read the thread in detail, but if replicating Access XP/2000 to Access
> XP/2000 is what you need then this does work over the internet using only
a
> standard IIS port. There's a Microsoft document 'Internet Synchronization
> with the Microsoft Jet Database Engine: A Technical Overview', which is a
> great help. I've had this working for about 2 years, and it does the job
but
> if you can you'll find SQL Server / MSDE replication more reliable and
> easier to manage. You don't really need replication manager. I have some
> code for our own 'replication scheduler' I'd be willing to share. Another
> good site for information is www.trigeminal.com, where you can find
another[vbcol=seagreen]
> 'replication manager' program.
> Tony.
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eEjmSFSgEHA.1972@.TK2MSFTNGP09.phx.gbl...
> the
this[vbcol=seagreen]
it[vbcol=seagreen]
useful[vbcol=seagreen]
available[vbcol=seagreen]
little[vbcol=seagreen]
books.
>
|||This is my understanding too. But if your application suits you may be able
to use DTS or some custom code to import/export data between the replicated
Access and SQL server DB's. Worked for us, but SQL server / MSDE replication
is a much better solution.
Much more info on microsoft.public.access.replication.
Tony
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uGkQluhgEHA.2984@.tk2msftngp13.phx.gbl...
> My understanding is that this allows you to replicate an Access Database
to[vbcol=seagreen]
> another Access Database over the internet. but does not allow you to
> replicate an Access Database to a SQL Server or vice versa over the
> internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tony Toker" <xxxx@.xxxx.com> wrote in message
> news:cfj3gr$k2s$1$830fa17d@.news.demon.co.uk...
Access[vbcol=seagreen]
only[vbcol=seagreen]
> a
Synchronization[vbcol=seagreen]
a[vbcol=seagreen]
> but
Another[vbcol=seagreen]
> another
via[vbcol=seagreen]
> this
so
> it
> useful
> available
> little
> books.
>

Access + ODBC + MS SQL on WAN

Hi, there !
Can anybody tell me why the application MS ACCESS 2002
front-end + MS SQL Server 2000 back-end with the tables
linked via ODBC is running perfectly well on LAN, and does
not run on WAN ?
The firewall and security issues excluded.
Many thanks,
Dmitri KalininWhat do you mean by 'Does not run'
Is it slow? Crashes?
"Dmitri" <kalinin@.dblink.co.nz> wrote in message
news:081301c33f95$46100d80$a101280a@.phx.gbl...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin|||"Dmitri" <kalinin@.dblink.co.nz> wrote in message news:<081301c33f95$46100d80$a101280a@.phx.gbl>...
> Hi, there !
> Can anybody tell me why the application MS ACCESS 2002
> front-end + MS SQL Server 2000 back-end with the tables
> linked via ODBC is running perfectly well on LAN, and does
> not run on WAN ?
> The firewall and security issues excluded.
> Many thanks,
> Dmitri Kalinin
I'd guess there are different network protocols involved somehow.
Or maybe DNS.
Perhaps you need a LMhosts file (update)?

Access .mdb link to SQL Server 2000

Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Se
rver 2000 Database via an ODBC connection. The connection is set to use SQL
Server authentication which works fine for myself but not for a domain user
who needs this.
When I launch the Database and run the query which links the Database I get
"Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc
sql server driver sql server login failed for user null; reason, not associa
ted with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get
the error message up, I can get round the problem by unticking the "Use Tru
sted Connection" and then inputting the SQL Server password for the user but
it should just run the query and display in Access.
Any advice would be great.
Thanks,
MichaelYou'll need to create a security group for the users that require access to
SQL Server. Give that security group necessary permissions to the database a
nd your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.co
..Ben-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.T
K2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Se
rver 2000 Database via an ODBC connection. The connection is set to use SQL
Server authentication which works fine for myself but not for a domain user
who needs this.
When I launch the Database and run the query which links the Database I get
"Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc
sql server driver sql server login failed for user null; reason, not associa
ted with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get
the error message up, I can get round the problem by unticking the "Use Tru
sted Connection" and then inputting the SQL Server password for the user but
it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael|||Thanks Steve - I'll give that a go and let you know how I get on.
"Steve Thompson" <stevethompson@.nomail.please> wrote in message news:OH$IIvO
GFHA.936@.TK2MSFTNGP12.phx.gbl...
You'll need to create a security group for the users that require access to
SQL Server. Give that security group necessary permissions to the database a
nd your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.co
..Ben-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.T
K2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Se
rver 2000 Database via an ODBC connection. The connection is set to use SQL
Server authentication which works fine for myself but not for a domain user
who needs this.
When I launch the Database and run the query which links the Database I get
"Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc
sql server driver sql server login failed for user null; reason, not associa
ted with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get
the error message up, I can get round the problem by unticking the "Use Tru
sted Connection" and then inputting the SQL Server password for the user but
it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael

Access .mdb link to SQL Server 2000

Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael
|||Thanks Steve - I'll give that a go and let you know how I get on.
"Steve Thompson" <stevethompson@.nomail.please> wrote in message news:OH$IIvOGFHA.936@.TK2MSFTNGP12.phx.gbl...
You'll need to create a security group for the users that require access to SQL Server. Give that security group necessary permissions to the database and your trusted connection should work.
Adding the security group is somewhat documented here: http://support.microsoft.com/default...en-us%3B889615
Steve
"Hoja, Michael" <Michho@.oink.co.uk> wrote in message news:uxRarDDFFHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi,
Can anyone help - I've got an Access 2002 Database which links to our SQL Server 2000 Database via an ODBC connection. The connection is set to use SQL Server authentication which works fine for myself but not for a domain user who needs this.
When I launch the Database and run the query which links the Database I get "Connection failed: Sqlstate: 28000 Sql server error: 18456 Microsoft odbc sql server driver sql server login failed for user null; reason, not associated with a trusted sql server.
I've installed the latest version of MDAC 2.8 which didn't help. When I get the error message up, I can get round the problem by unticking the "Use Trusted Connection" and then inputting the SQL Server password for the user but it should just run the query and display in Access.
Any advice would be great.
Thanks,
Michael

About SQL field Type

Hi Friends
I have question.
I connected oracle and MS access tables(via Delphi7).
I wanna get type of field in indicated table. For example if it is
integer or boolean e.t.c

Please tell me the SQL function or whatever that retrievs type of field.
Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ulan yrysbaev (ulasir@.yahoo.com) writes:
> Hi Friends
> I have question.
> I connected oracle and MS access tables(via Delphi7).
> I wanna get type of field in indicated table. For example if it is
> integer or boolean e.t.c
> Please tell me the SQL function or whatever that retrievs type of field.

Since you use different platforms, you should be using the
INFORMATION_SCHEMA views, which are part of the ANSI standard. But
I have no idea whether any of Access and Oracle supports them.

You are probably better off asking in comp.databases.ms-access and
comp.databases.oracle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

About reinitializing ....

This is to do with SQL 2005 merge replication (HTTPS if it matters)

Is there any difference between marking a subscription for reinitalizing via Snapshot Monitor, and having the subscriber mark it for reinitialization ?

Thanks
Bruce

Probably the same. See if what you're doing is the same as in BOL topic "How to: Reinitialize a Subscription (SQL Server Management Studio)".

about optimization?

There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables

however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?

thanks in advance

Moving to more appropriate forum...|||

Generally speaking, you usually don't want more than about 3-4 indexes on a table that is used for an OLTP workload. Rather than guessing, if you have SQL Server 2005, you can run the query below to see which indexes are being used. If you see indexes that have zero or a very low number of reads, then you should consider dropping those indexes.

Index Read/Write stats for a single table

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id,

reads = user_seeks + user_scans + user_lookups, writes = user_updates

FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.object_id = i.object_id

AND i.index_id = s.index_id

AND s.database_id = @.dbid

AND object_name(s.object_id) = 'YourTableName'

ORDER BY object_name(s.object_id), writes DESC, reads DESC;

|||

thank you very much

thus i can first tune indexs

then consider to stop index before etl, and rebuild index after that

About login into sql server via asp.net

I want to design a form with C# that will able me to log in to my sql server with my sql login name and password.This should be the entry point, which is like the "SqlWebAdmin", Where i login first then have a view of databases that i have permission. The problem first is how to login into the sql server via ASP .net without knowing prior which database is mine?Any help plz?Most apps put their connect information in web.config, and read it out at run time.