Accessing linked servers dynamically

We have a view in one database that consists of the union of all the
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!

Accessing linked server

Is there any way to access a linked server without using four-part naming?

My problem is that I am trying to find a way to connect to an access database on a 64 bit system (there is no 64 Jet OleDb provider) and I have to run in a 64 bit process so no WoW solutions will work for me .
I am looking into trying to use a Sql Linked server but am trying to find a workaround without having to re-write all our queries to use four-part naming.
Is there someway to configure a connectionstring to default to executing against a linked server?

Any help or suggestions would be welcomed.

You can create a synonym (if your server is SQL 2005) for a four-part name.
See CREATE SYNONYM.|||Brilliant thanks for that Anton,
Had a bit of a play and it seems to work nicely. I guess I will run into trouble if any of the queries contain VBA expressions but that gets me a long way.
I am going to make a dumby Sql Database that contains no objects only the synonyms for the tables and views that I need in the Access database, then I can create a connection to that dumby database for any of the Jet work I need to do.

As a thought: I guess given that the synonyms are entities of the Sql database then preprossing will occur at the Sql Server, therefore it is unlikely that you could configure seemless passthrough style execution (in terms of the Sql dialect to use) to the Jet Linked server because Sql will always want its own dialect rather than the Jet dialect.
For example if I wanted a PIVOT/Cross Tab query, I would need to decide to send a request to a precompiled Jet query that contained a 'TRANSFORM' statement or send a Sql String with the Sql Server Style 'PIVOT....FOR...' Statement. Would this be right?

Thanks
Simon

Accessing Linked Excel Server

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[OLE/DB provider returned message: Unspecified error]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

Hi, David,

The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:

== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again

== If you are using NT authentication, try using SQL authentication to see if this changes the effect

== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Active Directory using SQL Server 2000

Hello All,

I have a linked ADSI Server to our company Active Directory and
everything is fine. I'm running queries and such using LDAP. BUT how
can I aquire a list of attributes for the classes and categories. Such
as if i wanted to see a user's telephone number and email address for
use in a corporate database? Is this possible?

ThanksHi

I have never tried this.. but...:

The example of ADSI in books online returns the SN and ST attributes of the
contact class. See Books Online:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_12_94fn.htm

You should then be able to use the same OPENQUERY as the destination of the
update statement. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_oa-oz_5xix.htm

John

"Brandon Arnold" <btugg@.yahoo.com> wrote in message
news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> Hello All,
> I have a linked ADSI Server to our company Active Directory and
> everything is fine. I'm running queries and such using LDAP. BUT how
> can I aquire a list of attributes for the classes and categories. Such
> as if i wanted to see a user's telephone number and email address for
> use in a corporate database? Is this possible?
> Thanks|||Thanks John but I'm looking a little deeper than that.

While searching yesterday I ran across another attribute that is
usually shown
in sample LDAP queries and it was "sAMAccountNAme" which is the
account a user may log on with. I'm for a list of attributes for
Computers, People, etc in Active Directory. Is there such a list or
definition table?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> Hi
> I have never tried this.. but...:
> The example of ADSI in books online returns the SN and ST attributes of the
> contact class. See Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> ata.chm::/ac_8_qd_12_94fn.htm
> You should then be able to use the same OPENQUERY as the destination of the
> update statement. See Books Online:
> mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> lref.chm::/ts_oa-oz_5xix.htm
> John
> "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > Hello All,
> > I have a linked ADSI Server to our company Active Directory and
> > everything is fine. I'm running queries and such using LDAP. BUT how
> > can I aquire a list of attributes for the classes and categories. Such
> > as if i wanted to see a user's telephone number and email address for
> > use in a corporate database? Is this possible?
> > Thanks|||As in ?:
http://msdn.microsoft.com/library/d...ace_mapping.asp

John

"Brandon Arnold" <btugg@.yahoo.com> wrote in message
news:8508d156.0312310700.647abcfb@.posting.google.c om...
> Thanks John but I'm looking a little deeper than that.
> While searching yesterday I ran across another attribute that is
> usually shown
> in sample LDAP queries and it was "sAMAccountNAme" which is the
> account a user may log on with. I'm for a list of attributes for
> Computers, People, etc in Active Directory. Is there such a list or
> definition table?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> > Hi
> > I have never tried this.. but...:
> > The example of ADSI in books online returns the SN and ST attributes of
the
> > contact class. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> > ata.chm::/ac_8_qd_12_94fn.htm
> > You should then be able to use the same OPENQUERY as the destination of
the
> > update statement. See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> > lref.chm::/ts_oa-oz_5xix.htm
> > John
> > "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> > news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > > Hello All,
> > > > I have a linked ADSI Server to our company Active Directory and
> > > everything is fine. I'm running queries and such using LDAP. BUT how
> > > can I aquire a list of attributes for the classes and categories. Such
> > > as if i wanted to see a user's telephone number and email address for
> > > use in a corporate database? Is this possible?
> > > > Thanks|||Yes! Thanks John!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<L_TIb.3630$5X.31045680@.news-text.cableinet.net>...
> As in ?:
> http://msdn.microsoft.com/library/d...ace_mapping.asp
> John
> "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> news:8508d156.0312310700.647abcfb@.posting.google.c om...
> > Thanks John but I'm looking a little deeper than that.
> > While searching yesterday I ran across another attribute that is
> > usually shown
> > in sample LDAP queries and it was "sAMAccountNAme" which is the
> > account a user may log on with. I'm for a list of attributes for
> > Computers, People, etc in Active Directory. Is there such a list or
> > definition table?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:<ibyIb.2549$le.23310381@.news-text.cableinet.net>...
> > > Hi
> > > > I have never tried this.. but...:
> > > > The example of ADSI in books online returns the SN and ST attributes of
> the
> > > contact class. See Books Online:
> > > mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\acd
> > > ata.chm::/ac_8_qd_12_94fn.htm
> > > > You should then be able to use the same OPENQUERY as the destination of
> the
> > > update statement. See Books Online:
> > mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
> > > lref.chm::/ts_oa-oz_5xix.htm
> > > > John
> > > > "Brandon Arnold" <btugg@.yahoo.com> wrote in message
> > > news:8508d156.0312301444.b04f4c8@.posting.google.co m...
> > > > Hello All,
> > > > > > I have a linked ADSI Server to our company Active Directory and
> > > > everything is fine. I'm running queries and such using LDAP. BUT how
> > > > can I aquire a list of attributes for the classes and categories. Such
> > > > as if i wanted to see a user's telephone number and email address for
> > > > use in a corporate database? Is this possible?
> > > > > > Thanks

Access: linked tables invisible

I've recently moved a database from mssql2000 on an NT Server machine
to mssql2000 running on a Win 2003 Server machine.
Now, when I try to use Access 2002 to Link an sql table, the Access
wizard isn't showing any of the sql tables. In fact, it only shows a
couple of the system tables: dbo.sysconstraints & dbo.syssegments and
one of the views in the database.
I'm using odbc to connect Access to the SQL Server. Everyting else
seems to work ok.
Could anyone suggest whats wrong here, or how to go about finding out
whats wrong?
regards
Richard
On Fri, 06 May 2005 09:01:29 +0100, Richard <richard@.softmode.net>
wrote:
mssql2000 running on a Win 2003 Server machine.
>Now, when I try to use Access 2002 to Link an sql table, the Access
>wizard isn't showing any of the sql tables. In fact, it only shows a
>couple of the system tables: dbo.sysconstraints & dbo.syssegments and
>one of the views in the database.
>
Problem solved by installing latest mdac on workstation.
Richard

Access: linked tables invisible

I've recently moved a database from mssql2000 on an NT Server machine
to mssql2000 running on a Win 2003 Server machine.
Now, when I try to use Access 2002 to Link an sql table, the Access
wizard isn't showing any of the sql tables. In fact, it only shows a
couple of the system tables: dbo.sysconstraints & dbo.syssegments and
one of the views in the database.
I'm using odbc to connect Access to the SQL Server. Everyting else
seems to work ok.
Could anyone suggest whats wrong here, or how to go about finding out
whats wrong?
regards
RichardOn Fri, 06 May 2005 09:01:29 +0100, Richard <richard@.softmode.net>
wrote:
mssql2000 running on a Win 2003 Server machine.
>Now, when I try to use Access 2002 to Link an sql table, the Access
>wizard isn't showing any of the sql tables. In fact, it only shows a
>couple of the system tables: dbo.sysconstraints & dbo.syssegments and
>one of the views in the database.
>
Problem solved by installing latest mdac on workstation.
Richard

Access/SQL Linked Tables

I have linked a number of tables from a SQL database to a Access front end. I am able to update all but one of the tables (the main table I need!!!). I get the error, cannot update record as it has been changed by another user although there is no other user. I believe it may be to do with the way the recordset is linked to the access database or the referential integrity of the table associations in the SQL database.

I can change the data using query analyser without issue.

Any help would be greatfully apreciated.Are you changing the data through a form?
Can you change the data by opening up the linked table?|||I am using a form but cannot change the data in that or the linked table. The only place I have successfully changed data is through QM or directly in SQL server.|||Try dropping and recreating your table link. Verify permissions on your database table.|||I had this problem a while ago. Make sure that table has no triggers associated with it. This is very important. Are you using Access front end and linked SQL server tables, or is it a .ADP (Access Data Project)? If its not, you might want to look into upsizing and making it a .ADP file|||Okay, have checked all of the tables and there are no apparent triggers that I can see. I have removed and re-linked the table and also removed the properties to update referential integrity on the SQL database but still no luck. I am now looking into upsizing the database to an access data project. Although I am not sure how the linked tables will differ with this.|||I presume you specified a PK\ unique constraint in the BE or, if not, a candidate key when linking?|||Guy's, I have solved the problem by adding a timestamp field to the BE table and re-attaching to the FE.

Thanx for all your help :)

Access XP to SQL 7 Linked Tables

Basically, I want to 'link' the tables for record modifications, entries, et
c., so that the client (within a WAN) has the GUI front-end that he/she is a
ccustomed to (Access). ODBC seems old and slow, and I wanted to try somethi
ng new - ADO/ADOX/OLEDB? I
s there an easy way to make the connection, iterate through the tables (NOT
the sql system tables) that automatically includes the columns and maintains
that 'link' or connection while the AccessXP GUI front-end is open? This w
ould, of course, need to be
able to support multiple users at the same time.
Right now I get a "Compile error: user defined type not defined" on the fir
st line, but was hoping this was getting close...
THANK YOU!
(On-load event of start-up form.)
Public Function linkTables()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
Dim avarSourceTables() As Variant
avarSourceTables = Array("dbo_tblHandouts", _
"dbo_tblParticipants", _
"dbo_tblSiteNetworks", _
"dbo_tblSubEvent", _
"dbo_tblSubReceivables", _
"dbo_xContacts", _
"dbo_xSites", _
"dbo_xTblEventStatus", _
"dbo_xTblNetwork", _
"dbo_xTblPartStatus", _
"dbo_xTblRate", _
"dbo_xTblRateSubType", _
"dbo_xTblReceivables", _
"dbo_xTblRequestors", _
"dbo_xTblRoomStyle", _
"dbo_xTblReports", _
"dbo_xTblSites", _
"dbo_xTblType")
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=servernameC;" & _
"Database=databasename;" & _
"Trusted_Connection=Yes;" & _
"Uid=oops;" & _
"Pwd=password;"
' Create a new Table object
For i = LBound(avarSourceTables) To UBound(avarSourceTables)
Set oTable = New ADOX.Table
With oTable
.Name = avarSourceTables(i)
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Next i
End Function
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...If you choose to use ADO you need to create your recordsets in code and not
rely on linked tables, which are not supported by ADO...
Steve
"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:uTQj5tO4DHA.1644@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Basically, I want to 'link' the tables for record modifications, entries,

---

etc., so that the client (within a WAN) has the GUI front-end that he/she is
accustomed to (Access). ODBC seems old and slow, and I wanted to try
something new - ADO/ADOX/OLEDB? Is there an easy way to make the
connection, iterate through the tables (NOT the sql system tables) that
automatically includes the columns and maintains that 'link' or connection
while the AccessXP GUI front-end is open? This would, of course, need to be
able to support multiple users at the same time.

quote:

---

> Right now I get a "Compile error: user defined type not defined" on the

---

first line, but was hoping this was getting close...

quote:

---

> THANK YOU!
>
> (On-load event of start-up form.)
> Public Function linkTables()
> Dim oCat As ADOX.Catalog
> Dim oTable As ADOX.Table
> Dim sConnString As String
> Dim avarSourceTables() As Variant
> avarSourceTables = Array("dbo_tblHandouts", _
> "dbo_tblParticipants", _
> "dbo_tblSiteNetworks", _
> "dbo_tblSubEvent", _
> "dbo_tblSubReceivables", _
> "dbo_xContacts", _
> "dbo_xSites", _
> "dbo_xTblEventStatus", _
> "dbo_xTblNetwork", _
> "dbo_xTblPartStatus", _
> "dbo_xTblRate", _
> "dbo_xTblRateSubType", _
> "dbo_xTblReceivables", _
> "dbo_xTblRequestors", _
> "dbo_xTblRoomStyle", _
> "dbo_xTblReports", _
> "dbo_xTblSites", _
> "dbo_xTblType")
> ' Set SQL Server connection string used in linked table.
> sConnString = "ODBC;" & _
> "Driver={SQL Server};" & _
> "Server=servernameC;" & _
> "Database=databasename;" & _
> "Trusted_Connection=Yes;" & _
> "Uid=oops;" & _
> "Pwd=password;"
> ' Create a new Table object
> For i = LBound(avarSourceTables) To UBound(avarSourceTables)
> Set oTable = New ADOX.Table
> With oTable
> .Name = avarSourceTables(i)
> Set .ParentCatalog = oCat
> .Properties("Jet OLEDB:Create Link") = True
> .Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
> .Properties("Jet OLEDB:Link Provider String") = sConnString
> End With
> ' Add Table object to database
> oCat.Tables.Append oTable
> oCat.Tables.Refresh
> Next i
> End Function
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &

---

ASP.NET resources...|||Thanks so much for the straight-forward answer Steve. Poop!-is my response.
But, at least I'm not getting things like "You need to make sure you've se
lected the right library." and I keep trying to tweak and search forever.
Thanks again. I'll go back to the odbc file dsn.
Speaking of which, what I thought I could avoid in the first place, I've got
the mdb on a file server and the sql odbc file dsn in the same folder. Two
people can open the file fine, but one gets an odbc failed error with no co
de or description. All are
running same os, same office version, with same version of sql odbc driver (
2000.81.9042.00)
Any clues?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:u01g9bP4DHA.1816@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Thanks so much for the straight-forward answer Steve. Poop!-is my

---

response. But, at least I'm not getting things like "You need to make sure
you've selected the right library." and I keep trying to tweak and search
forever.

quote:

---

> Thanks again. I'll go back to the odbc file dsn.
> Speaking of which, what I thought I could avoid in the first place, I've

---

got the mdb on a file server and the sql odbc file dsn in the same folder.
Two people can open the file fine, but one gets an odbc failed error with no
code or description. All are running same os, same office version, with
same version of sql odbc driver (2000.81.9042.00)

quote:

---

> Any clues?

---

Back when I was doing Access development, then migrated to Access - SQL
Server integration, I always preferred placing Access on the desktop for a
"true" client/server solution. While it takes more time, work, and testing
to get it right you'll have a far more robust solution.
If you have not discovered this book, I highly recommend getting this:
Microsoft Access Developer's Guide to SQL Server
by Andy Baron & Mary Chipman <SAMS>
ISBN: 0672319446
Steve

Access table link to SQL question

I have a SQL 7 database I want to link to from Access 2000. Previously this
database could be linked to over our local network by way of a WAN link. We'
ve just moved and now that WAN link has been severed and so I'm forced to re
establish the table link ov
er the internet. The SQL server and website exist on the same box and when I
enter the public IP address of the server into the input field labeled "wha
t server do you want to connect to?" in DSN setup, it won't accept it. What
do I have to do to establis
h a dsn to a database over the internet as opposed to a local network?One option would be to add an entry to your host file for
the server and it's IP address. Then configure the DSN with
the server name you put in the host file for the server.
-Sue
On Thu, 18 Mar 2004 07:46:05 -0800, "Glenn"
<gvenzke@.equiguard.com> wrote:

>I have a SQL 7 database I want to link to from Access 2000.
>Previously this database could be linked to over our local network
>by way of a WAN link. We've just moved and now that WAN link
>has been severed and so I'm forced to reestablish the table link
>over the internet. The SQL server and website exist on the same
>box and when I enter the public IP address of the server into the
>input field labeled "what server do you want to connect to?" in DSN
>setup, it won't accept it. What do I have to do to establish a dsn to a
>database over the internet as opposed to a local network?

Access sometimes won't use index of linked view

Hello,
I have a weird problem with sql server 2000. I have a view wich
combines 2 tables like this:
SELECT foo
FROM dbo.tblA LEFT OUTER JOIN
dbo.tblB ON dbo.A.ID = dbo.B.ID
Table B contains a field named fldSorting, wich has an index. It is
used - you have already guessed it by now - for sorting.
The view, linked with odbc, is the datasource on a form in ms access
like this: SELECT * FROM foo ORDER BY fldSorting;
This does work. However, for some weird reason, this view becomes
very, VERY slow from time to time (> 1 minute). This happens once in
three months or something. I have looked with the profiler what
exactly access is querying, and that is just normal: SELECT * FROM foo
ORDER BY fldSorting. If i execute this in the query analyzer, it is as
fast as always (<2 seconds)! This is extremely weird, right?
I have found a way to make it quick again, but only for a couple of
months: remove the index on field fldSorting and create it again.
How comes? I have absolutely no clue whatsoever.
1. Why does this problem pop up sometimes?
2. Why does sometimes the exact same query seems to not use an index
if access executes it via odbc, while executed via the query analyzer,
all is ok?
This is a very unpleasant problem since it is our main sql server, and
our ERP system is also running on this server. So every time this
happens, everybody has a break...
Does anybody has some ideas? I will be very happy with all thoughts...
Thanks in advance,
B. Lowsma
Netherlands
Being that the problem goes away when you drop and recreate
the index and then it comes back again in a couple of
months, I'd start looking into the possibility that the
index is fragmented when you start hitting the problems.
-Sue
On Fri, 29 Jun 2007 07:29:19 -0000, "meatusenet@.gmail.com"
<meatusenet@.gmail.com> wrote:

>Hello,
>I have a weird problem with sql server 2000. I have a view wich
>combines 2 tables like this:
>SELECT foo
>FROM dbo.tblA LEFT OUTER JOIN
> dbo.tblB ON dbo.A.ID = dbo.B.ID
>Table B contains a field named fldSorting, wich has an index. It is
>used - you have already guessed it by now - for sorting.
>The view, linked with odbc, is the datasource on a form in ms access
>like this: SELECT * FROM foo ORDER BY fldSorting;
>This does work. However, for some weird reason, this view becomes
>very, VERY slow from time to time (> 1 minute). This happens once in
>three months or something. I have looked with the profiler what
>exactly access is querying, and that is just normal: SELECT * FROM foo
>ORDER BY fldSorting. If i execute this in the query analyzer, it is as
>fast as always (<2 seconds)! This is extremely weird, right?
>I have found a way to make it quick again, but only for a couple of
>months: remove the index on field fldSorting and create it again.
>How comes? I have absolutely no clue whatsoever.
>1. Why does this problem pop up sometimes?
>2. Why does sometimes the exact same query seems to not use an index
>if access executes it via odbc, while executed via the query analyzer,
>all is ok?
>This is a very unpleasant problem since it is our main sql server, and
>our ERP system is also running on this server. So every time this
>happens, everybody has a break...
>Does anybody has some ideas? I will be very happy with all thoughts...
>Thanks in advance,
>B. Lowsma
>Netherlands
|||Thanks for your reply. So if this occurs i can possibly fix it with
DBCC DBREINDEX or DBCC INDEXDEFRAG? I will try that the first time it
happens again... If this is the case, is there anything i can do to
prevent this from happening?
TIA,
B. Lowsma
On 6 jul, 05:27, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> Being that the problem goes away when you drop and recreate
> the index and then it comes back again in a couple of
> months, I'd start looking into the possibility that the
> index is fragmented when you start hitting the problems.
> -Sue
> On Fri, 29 Jun 2007 07:29:19 -0000, "meatuse...@.gmail.com"
> <meatuse...@.gmail.com> wrote:
>
>
>
>
|||Sure...read up on the subject in books online under the help
topic for DBCC SHOWCONTIG. You want to be doing some type of
index maintenance on a regular basis but you do want to take
other things into consideration with defragmentation of
indexes. This is a good source of information on the
subject, what to consider, etc:
Microsoft SQL Server 2000 Index Defragmentation Best
Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
-Sue
On Fri, 06 Jul 2007 13:11:12 -0000, "meatusenet@.gmail.com"
<meatusenet@.gmail.com> wrote:

>Thanks for your reply. So if this occurs i can possibly fix it with
>DBCC DBREINDEX or DBCC INDEXDEFRAG? I will try that the first time it
>happens again... If this is the case, is there anything i can do to
>prevent this from happening?
>TIA,
>B. Lowsma
>On 6 jul, 05:27, Sue Hoegemeier <S...@.nomail.please> wrote:
>
|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> Sure...read up on the subject in books online under the help
> topic for DBCC SHOWCONTIG. You want to be doing some type of
> index maintenance on a regular basis but you do want to take
> other things into consideration with defragmentation of
> indexes. This is a good source of information on the
> subject, what to consider, etc:
> Microsoft SQL Server 2000 Index Defragmentation Best
> Practices
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> -Sue
> On Fri, 06 Jul 2007 13:11:12 -0000, "meatusenet@.gmail.com"
> <meatusenet@.gmail.com> wrote:

Access Projects, SQL Server, and 1-to-1 joins

Hello everyone,

I'm working on porting an Access Application with linked tables to an Access Project, to make it faster. The backend is (unsurprisingly) MS SQL Server.

My problem is thisL:

I have a form (well, several forms) which are sourced from a 1 - 1 join
Person <-> Candidate

It seems that I can only edit one of these tables at a time, the one specified by the UniqueTable property. I want to have access to the whole record at once. In the Access documentation, it doesn't seem to mention this case, which seems a little odd.

Adding records is not a problem, it's only updating, and it can be guaranteed that a Clienta and Person record both exist.

I don't think I can enforce referential integrety, as Person also links to Client, so a Person record might not match to a Candidate record.

How can I set up the database or form to permit the editing of both tables simulatenously?

Thanks,
SamJust a suggestion but while you are porting this thing over why not de-normalize these tables into one table with a Type field (person, client, candidate)?

This will make life much easier...|||The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Hi,
I was searching and found your thread. I am having the exact same problem. I have a "Person" table and other subtypes of it like "Contatc".
On my contact form I have a record source with a 1-1 join on tblContact and tblPerson (when a user is inserting a contact a VB code would insert the new assigned number from the Person table to the Contact table)

Now that I'm trying to use Access projects it won't work. Its driving me nuts. I've tried every trick I know and it won't work.

By the way I am very suprised that my ODBC connection is much faster than my Access project, at least for data entry. When I'm using Access project and when I start typing in a field it pause for a second and then the characters apear. Thats wierd, do you know any thing about it?

Originally posted by Digitaleus
The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||You mean instead of having a join query as the record source of the form, us a wizard to sync them (we can also drag and drop)?

Thanks

Originally posted by chrisp_999
Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||Assume you have frm_person and frm_contact as subform of frm_contact.

Datasource for frm_contact is
select * from tbl_person

Assumed you have a field ID in tbl_person, datasource for frm_contact is

select * from tbl_contact where personID = @.ID

and the Input Parameter property for frm_contact is

@.ID int = forms!frm_person!ID
(or txt_ID or whatever the name of the field is)

Anytime the record on frm_person is changed, issue (in VBA)
forms!frm_contact.requery.

That's the basic scheme. Details vary depending on wether you have an endless form or not and so on.

BTW, with MSSQL you can use a trigger to create the entry in tbl_contact instead of VBA. So you ensure data integrity within the database (e.g. if you insert manually).|||correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.|||Thanks

Originally posted by chrisp_999
correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.

Access ODBC Linked tables & RS report question

Given some Access ODBC linked tables and the request for a RS report, what can be done technically? Run those linked tables directly in a RS report? Or use them via SQL server? How anyway? Thanks much for any help.A linked server is being created with all the said tables. Has anyone used a linked server and run RS reports against it? Thanks for any advice.

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.
Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish|||You could set it up using a view and a linked server rather than a linked ta
ble. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.
Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
Thanks
Yogish
|||You could set it up using a view and a linked server rather than a
linked table. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around
CecilCable
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message922826.html

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
--
Thanks
Yogish|||You could set it up using a view and a linked server rather than a
linked table. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around
CecilCable
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message922826.html

Access linked to SQL Server - 2 day offset error

We recently translated the backend db from Access(97) to SQL Server.
We are still using Access frontends. I have an update query in the
Access front end that uses a lookup table to populate fields. The
common fields between the table and the lookup table are the primary
key (LocID) and date & time fields. The query is:

UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
tblPT.Salinity = [tblPT_Offsets].[Salinity]
WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
[EndDate]));

This worked fine in Access and seemed to work fine after switching to
Access, but on closer look, there is exactly a 2 day error being
introduced. A quick search of the newsgroups brings up lots of Access
to SQL date problems, but a 2 day offset seems rather strange? Any
ideas??

I know the field names Date and Time are inappropriate, but legacy
issues are a pain in the butt to resolve!! Could this be a problem?

Davidarchean1@.yahoo.com (David) wrote in message news:<31e424c8.0405251702.14950a8f@.posting.google.com>...
> We recently translated the backend db from Access(97) to SQL Server.
> We are still using Access frontends. I have an update query in the
> Access front end that uses a lookup table to populate fields. The
> common fields between the table and the lookup table are the primary
> key (LocID) and date & time fields. The query is:
> UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
> tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
> tblPT.Salinity = [tblPT_Offsets].[Salinity]
> WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
> ((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
> [EndDate]));
> This worked fine in Access and seemed to work fine after switching to
> Access, but on closer look, there is exactly a 2 day error being
> introduced. A quick search of the newsgroups brings up lots of Access
> to SQL date problems, but a 2 day offset seems rather strange? Any
> ideas??
> I know the field names Date and Time are inappropriate, but legacy
> issues are a pain in the butt to resolve!! Could this be a problem?
> David

Can you post some sample data to show the problem? It's not really
clear from the details above what you're seeing. Are you using the
query above with linked tables in Access?

Simon|||On 25 May 2004 18:02:25 -0700, David wrote:

>We recently translated the backend db from Access(97) to SQL Server.
>We are still using Access frontends. I have an update query in the
>Access front end that uses a lookup table to populate fields. The
>common fields between the table and the lookup table are the primary
>key (LocID) and date & time fields. The query is:
>UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =
>tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],
>tblPT.Salinity = [tblPT_Offsets].[Salinity]
>WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND
>((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And
>[EndDate]));
>This worked fine in Access and seemed to work fine after switching to
>Access, but on closer look, there is exactly a 2 day error being
>introduced. A quick search of the newsgroups brings up lots of Access
>to SQL date problems, but a 2 day offset seems rather strange? Any
>ideas??
>I know the field names Date and Time are inappropriate, but legacy
>issues are a pain in the butt to resolve!! Could this be a problem?
>David

Hi David,

I don't know if there is any relation at all, but just yesterday I
answered a question in another newsgroup about a 2-day difference being
introduced when copying data between SQL Server and Excel. That poster
seemed to use the internal date representation instead of formatted dates.
Your query does contain code to format the date, so it should not cause
this effect - but the fact your time difference is 2 days as well does
strike me as funny.

In case you want to check it out, follow the link below.

http://www.google.com/groups?q=exce...04ax.com&rnum=1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funnily enough, I did fix my problem with a simple fix, but I'm glad
to understand the problem. (Simon, the queries are in Access
frontends and the tables are linked via ODBC to a SQL Server.)

The sql code I posted (from the access frontend) actually already had
the fix. I had already changed

([Date] + [Time])

to ((Format([Date]+[Time],"mm/dd/yy hh:nn"))

and this fixed the problem. After reading the replies (Hugo, you hit
it on the head), it seems that passing SQL Server dates formatted both
as dates and double and then having SQL Server do date lookups, etc.
is dangerous. But by making sure all dates are passed as dates, I'm
assuming the ODBC translation takes care of the differences in the
date numbering scheme of Access/SQL. I think that sums it up?

thanks!
David

Access Linked Tables, monitoring SQL

We have a table that is populated via Access linked tables with forms bound
to it. When monitoring the table(or all SQL against the database) I don't
see any of the commands (CRUD) against this table. I beleive that
client-side cursors are used in this scenario? I'm depsarate to see the SQL
as we have disappearing data. Any thonughts or suggestions?
Have you tried a Profiler trace? That will show you the commands that
Access is executing against the server.
--Mary
On Thu, 18 Jan 2007 09:01:03 -0800, Jeff Ericson
<JeffEricson@.discussions.microsoft.com> wrote:

>We have a table that is populated via Access linked tables with forms bound
>to it. When monitoring the table(or all SQL against the database) I don't
>see any of the commands (CRUD) against this table. I beleive that
>client-side cursors are used in this scenario? I'm depsarate to see the SQL
>as we have disappearing data. Any thonughts or suggestions?

Access Linked Tables, monitoring SQL

We have a table that is populated via Access linked tables with forms bound
to it. When monitoring the table(or all SQL against the database) I don't
see any of the commands (CRUD) against this table. I beleive that
client-side cursors are used in this scenario? I'm depsarate to see the SQ
L
as we have disappearing data. Any thonughts or suggestions?Have you tried a Profiler trace? That will show you the commands that
Access is executing against the server.
--Mary
On Thu, 18 Jan 2007 09:01:03 -0800, Jeff Ericson
<JeffEricson@.discussions.microsoft.com> wrote:

>We have a table that is populated via Access linked tables with forms bound
>to it. When monitoring the table(or all SQL against the database) I don't
>see any of the commands (CRUD) against this table. I beleive that
>client-side cursors are used in this scenario? I'm depsarate to see the S
QL
>as we have disappearing data. Any thonughts or suggestions?