Access 2003 Project Connecting to SQL 2000 & 2005 Servers

We have an Access 2003 Project that it's primary server is a SQL 2000 server. When I link a table to the 2000 server and execute the view, I get data with no problem. I link a table to the 2005 server and execute the view...once again, no problem. However, if I try to write a query to pull data from both views......times out. I have changed the timeout value to "0" and never returns the data.

So then I created a new Access 2003 Project to use the 2005 as the primary server. Once again I create a linked table and execute the view on the SQL 2000 server and it works just fine. Then create a linked table to the 2005 server and the view works just fine. However, here's the difference. When I try to create a query to pull data from both views, I get:

1. This version of Microsoft Office Access doesn’t support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft Office Update Web site for the latest information and downloads. Your design changes will not be saved.

2. You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected. You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools.

Any assistance would be GREATLY appreciated. I'm stuck and wish I could make this happen.

PS:....

The version of Access 2003 specifically is: Microsoft ? Office Access 2003 (11.6566.6568) SP2

I've even made two linked tables to the 2005 server and executed each view by themselves and it worked fine. However, if I try to create a query off of those linked tables, same errors just posted above.

I'm afraid that Access ADPs aren't going to work all that smoothly with SQL Server 2005--they are created for SQL Server 2000 and earlier. If you're creating a new app, it's recommended that you use an mdb with linked tables because it gives you more flexibility. ADPs were designed around the scenario that you would connect to *one* SQL Server database, not multiple databases. One option if you must use ADPs is to create views to access data in another database. You can then use those views in your Access FE.

--Mary

sql

Access

I have an Access file with many tables and each table has got many constraints.I want to remove each and every constraints(including primary key,foreign relationship) for every table.How can I do it first?Delete the file

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
wonder[vbcol=seagreen]
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> for
> a
> wonder
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
Ellis
You'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>
|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a[vbcol=seagreen]
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
wonder
>
|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> for
> a
> wonder
>
|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>
|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> > Dear all,
> >
> > I use the identity property that makes generating unique numeric
> > values in my table "OrderDetail". Its primary keys include the column
> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
wonder
> > that if the application keep going to use for many years. Is it possible
> > the
> > identity value will be overflowed? or it will restart from 1 again
> > automatically after the overflow. And now, i make this column type is
> > "Int".
> > Anyone can help? Thanks.
> >
> > Best Rdgs
> > Ellis
> >
> >
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
> for
>> the data type If this is a possibility in your situation, consider using
> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective :)
--
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective :)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>> Hello Ellis,
>> from books online:
>> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
>> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
>> integer.
>>
>> Regards,
>> Tomislav Kralj
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int
>> data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum
>> value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>>
>