By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,226 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,226 IT Pros & Developers. It's quick & easy.

Questions about tables upsized db from access to sql

P: n/a
I have an Access DB that I upsized to a SQL server DB. The tables that I
upsized I can't seem to modify. I wanted to insert some data into the table
and I am getting the following error:

---Begin Error---
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'SVC_Details' does not match with a table name or alias
name used in the query.
---End Error---

The table called SVC_Details is the table that was upsized from Access. The
table is listed in both the query analyzer and sql enterprise manager. I do
not know why I am getting this error.

I am using the following code to insert the data:

---Begin Code---
insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
---End Code---

I am running MS-SQL 2000 standard on a Windows 2003 Standard server.

Thanks in advance for all the help!!
Nov 13 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
Hi!
In every table in the SQL-Server-databases which I link in Access, I
insert a field with type "timestamp" because Access has a problem with
different field-types. By updating the records wtihout timestamp-field
I got always errors like you.
pp

Nov 13 '05 #2

P: n/a
pp**@gmx.de wrote in news:1130493037.177995.202930
@g47g2000cwa.googlegroups.com:
Hi!
In every table in the SQL-Server-databases which I link in Access, I
insert a field with type "timestamp" because Access has a problem with
different field-types. By updating the records wtihout timestamp-field
I got always errors like you.
pp


??? I dont understand. What would a time stamp have to do with not being
able to update the data? Can you give a link to an example or post one here
for me to look at?

Thanks Mike
Nov 13 '05 #3

P: n/a
Mike wrote:
pp**@gmx.de wrote in news:1130493037.177995.202930
@g47g2000cwa.googlegroups.com:
Hi!
In every table in the SQL-Server-databases which I link in Access, I
insert a field with type "timestamp" because Access has a problem
with different field-types. By updating the records wtihout
timestamp-field I got always errors like you.
pp


??? I dont understand. What would a time stamp have to do with not
being able to update the data? Can you give a link to an example or
post one here for me to look at?

Thanks Mike


In a nutshell...

First you have to understand what a Timestamp field is in SQL Server. It is not
the same as a Timestamp in other database engines. It is a binary value that is
guaranteed to be unique across the entire database and which is updated on every
edit to the row. This column will always "appear" empty and is not for use by
any process other than the engine.

When you commit a change to a SQL Server linked table Access will check to see
if the record has been altered by other processes since being loaded into the
edit buffer. This is done by examining the Timestamp column's value (if one is
present) because a Timestamp is changed every time the record is changed (it's a
nice shortcut).

When there is no Timestamp column Access has to compare the value in every field
to what's in the buffer. If the table includes memo (SQL Server text) or any
DataType that doesn't translate exactly to an Access/Jet DataType Access can
come to the conclusion that the record has been altered even when it has not.
Since the problem depends on DataType differences you don't need one in EVERY
SQL Server table, only those with certain DataTypes in them. Many developers
though have gotten into the habit of including one all the time.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #4

P: n/a
Mike wrote:
I have an Access DB that I upsized to a SQL server DB. The tables that I
upsized I can't seem to modify. I wanted to insert some data into the table
and I am getting the following error:

---Begin Error---
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'SVC_Details' does not match with a table name or alias
name used in the query.
---End Error---

The table called SVC_Details is the table that was upsized from Access. The
table is listed in both the query analyzer and sql enterprise manager. I do
not know why I am getting this error.

I am using the following code to insert the data:

---Begin Code---
insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
---End Code---


The message is correct in that SVC_Details is not one of your tables in
the select statement.

you want something like:

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr left join
SVC_Details on svc_details_chrg_mastr.svccode = SVC_Details.svccode
Where SVC_Details.svccode is null;

or

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where not exists(Select * from SVC_Details a where
a.svccode=svc_details_chrg_mastr.svccode)

or

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svccode not in (select svccode from svc_details_chrg_mastr)

If executing on the server (i.e. passthrough) then the middle one may be
more efficient, it's definately more efficient than the 3rd (which is
easier to write). If executing as an Access query the middle one would
be the least efficient.

BTW: It's nothing to do with Timestamps :-)
Nov 13 '05 #5

P: n/a
Rick Brandt wrote:

[timestamp]
It is a binary value that is
guaranteed to be unique across the entire database


Is it unique? The name "Timestamp" is actually a misnomer my MS, the
SQL-92 data-type is "Rowversion" and T-SQL supports the use of that
keyword even though Enterprise Mangler does not.

I see no reason why it would have to be unique across the database, it
doesn't have to be unique at all, it just (as you've said) be different
each time the row is updated. For all intents and purposes it could be a
number that increments with each update. It can be the same value as a
timestamp in another table and in another row of the same table and
still perform it's function. The only uniqueness it needs is to not use
a value that's been used previously on that row of that table.
Nov 13 '05 #6

P: n/a
Trevor Best wrote:
Rick Brandt wrote:

[timestamp]
It is a binary value that is
guaranteed to be unique across the entire database


Is it unique? The name "Timestamp" is actually a misnomer my MS, the
SQL-92 data-type is "Rowversion" and T-SQL supports the use of that
keyword even though Enterprise Mangler does not.

I see no reason why it would have to be unique across the database, it
doesn't have to be unique at all, it just (as you've said) be
different each time the row is updated. For all intents and purposes
it could be a number that increments with each update. It can be the
same value as a timestamp in another table and in another row of the
same table and still perform it's function. The only uniqueness it
needs is to not use a value that's been used previously on that row
of that table.


I agree that in this context there is no requirement for uniqueness across the
database, but Books-On-Line indicates that it is unique. It also indicates that
it will eventually be renamed rowversion to conform to the standard.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #7

P: n/a

"Trevor Best" <no****@localhost.invalid> wrote in message
news:43*********************@news.zen.co.uk...
Mike wrote:
I have an Access DB that I upsized to a SQL server DB. The tables that I
upsized I can't seem to modify. I wanted to insert some data into the
table and I am getting the following error:

---Begin Error---
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'SVC_Details' does not match with a table name or alias
name used in the query.
---End Error---

The table called SVC_Details is the table that was upsized from Access.
The table is listed in both the query analyzer and sql enterprise
manager. I do not know why I am getting this error.

I am using the following code to insert the data:

---Begin Code---
insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
---End Code---


The message is correct in that SVC_Details is not one of your tables in
the select statement.

you want something like:

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr left join
SVC_Details on svc_details_chrg_mastr.svccode = SVC_Details.svccode
Where SVC_Details.svccode is null;

or

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where not exists(Select * from SVC_Details a where
a.svccode=svc_details_chrg_mastr.svccode)

or

insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svccode not in (select svccode from svc_details_chrg_mastr)

If executing on the server (i.e. passthrough) then the middle one may be
more efficient, it's definately more efficient than the 3rd (which is
easier to write). If executing as an Access query the middle one would be
the least efficient.

BTW: It's nothing to do with Timestamps :-)


I know the SVC_Details is not one of the tables in the select statement. I
am selecting data from a table called svc_details_chrg_mastr and I want to
insert the data into the SVC_Details table. The data I am inserting is new
data and not in the SVC_Details table. I am adding new records. The
SVC_Details has 15 columns and I want to insert data into 3 of the 15. Is
this the problem? That I want to insert only 3 columns and not all 15? All
the columns allow null values.

Mike
m charney at dunlap hospital dot org
Nov 13 '05 #8

P: n/a
Mike wrote:
I know the SVC_Details is not one of the tables in the select statement. I
am selecting data from a table called svc_details_chrg_mastr and I want to
insert the data into the SVC_Details table. The data I am inserting is new
data and not in the SVC_Details table. I am adding new records. The
SVC_Details has 15 columns and I want to insert data into 3 of the 15. Is
this the problem? That I want to insert only 3 columns and not all 15? All
the columns allow null values.


You have to look at it like this, an append query has two halves
First Half
Insert into table (column [,Columnn])

Second half
Select Column [,Columnn] from table

The second half must be able to be executed on its own so in your case,
you referenced the target table, since the target table does not exist
in the second half (the select statement) you got the error. You simply
cannot just reference the table named in the first half.
Nov 13 '05 #9

P: n/a

"Trevor Best" <no****@localhost.invalid> wrote in message
news:43***********************@news.zen.co.uk...
Mike wrote:
I know the SVC_Details is not one of the tables in the select statement.
I am selecting data from a table called svc_details_chrg_mastr and I want
to insert the data into the SVC_Details table. The data I am inserting is
new data and not in the SVC_Details table. I am adding new records. The
SVC_Details has 15 columns and I want to insert data into 3 of the 15. Is
this the problem? That I want to insert only 3 columns and not all 15?
All the columns allow null values.


You have to look at it like this, an append query has two halves
First Half
Insert into table (column [,Columnn])

Second half
Select Column [,Columnn] from table

The second half must be able to be executed on its own so in your case,
you referenced the target table, since the target table does not exist in
the second half (the select statement) you got the error. You simply
cannot just reference the table named in the first half.


Ok I took the Select statement from the Insert query and ran it by its self
and it executed fine. I guess I don't understand this at all. The select
statement from my insert query works fine when run by itself, but when I use
it in the insert query it does not function. The only reason I can figure
this is not working is because of the Where statement. I need to insert the
records that don't exist in the SVC_Details table FROM the
svc_details_chrg_mastr table.

I am sorry I am still not getting this, can you send me a link to something
that I can view on it?

Mike
Nov 13 '05 #10

P: n/a
The obvious question no one has asked is - did you make a link in your Access
database to the upsized table? You said you could see it in Query Analyzer
and Enterprise Manager, but can you see it in your Access database?

Also, are you trying to use an MDB or an ADP Access project?

On Thu, 27 Oct 2005 20:28:37 GMT, "Mike" <no***@forgetit.com> wrote:
I have an Access DB that I upsized to a SQL server DB. The tables that I
upsized I can't seem to modify. I wanted to insert some data into the table
and I am getting the following error:

---Begin Error---
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'SVC_Details' does not match with a table name or alias
name used in the query.
---End Error---

The table called SVC_Details is the table that was upsized from Access. The
table is listed in both the query analyzer and sql enterprise manager. I do
not know why I am getting this error.

I am using the following code to insert the data:

---Begin Code---
insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
---End Code---

I am running MS-SQL 2000 standard on a Windows 2003 Standard server.

Thanks in advance for all the help!!


Nov 13 '05 #11

P: n/a
I am using ADP, and I can see the tables in MS-Access.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:8e********************************@4ax.com...
The obvious question no one has asked is - did you make a link in your
Access
database to the upsized table? You said you could see it in Query
Analyzer
and Enterprise Manager, but can you see it in your Access database?

Also, are you trying to use an MDB or an ADP Access project?

On Thu, 27 Oct 2005 20:28:37 GMT, "Mike" <no***@forgetit.com> wrote:
I have an Access DB that I upsized to a SQL server DB. The tables that I
upsized I can't seem to modify. I wanted to insert some data into the
table
and I am getting the following error:

---Begin Error---
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'SVC_Details' does not match with a table name or alias
name used in the query.
---End Error---

The table called SVC_Details is the table that was upsized from Access.
The
table is listed in both the query analyzer and sql enterprise manager. I
do
not know why I am getting this error.

I am using the following code to insert the data:

---Begin Code---
insert into SVC_Details(svccode, svcdesc, price)
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
---End Code---

I am running MS-SQL 2000 standard on a Windows 2003 Standard server.

Thanks in advance for all the help!!

Nov 13 '05 #12

P: n/a
Mike wrote:
Ok I took the Select statement from the Insert query and ran it by its self
and it executed fine. I guess I don't understand this at all. The select
statement from my insert query works fine when run by itself, but when I use
it in the insert query it does not function. The only reason I can figure
this is not working is because of the Where statement. I need to insert the
records that don't exist in the SVC_Details table FROM the
svc_details_chrg_mastr table.
I fail to see how the following:
<---
Select svccode, svcdesc, svcprice from svc_details_chrg_mastr
Where svc_details_chrg_mastr.svccode <> SVC_Details.svccode
--->

Can ever work.
I am sorry I am still not getting this, can you send me a link to something
that I can view on it?


I already posted 3 different append queries that will work in your
application given the information you've fed so far. Can't you use one
of them?
Nov 13 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.