August 20, 2003 at 9:44 am
I am using an Access 2000 Project front end with a SQL Server backend. I used Access - Get External Data/Import/ODBC Databases, to import tables from two separate SQL Server databases (one off-the-shelf program, one in-house program).
I cannot modify the off-the-shelf program. SQL Server defines the off-the-shelf program’s table column data type as a varchar 12. When the table is imported into the Access Project, Access lists the column data type as an nvarchar 12.
When I create a form and try to join the off-the-shelf program’s table (Parent) with the in-house table’s column in a subform (child), it states "You are trying to link fields with incompatible data types". I tried changing the in-house program’s column to both a varchar 12 and an nvarchar 12 with the same results.
Any suggestions?
August 20, 2003 at 9:35 pm
Not sure I follow - shouldnt you be linking the table and not importing? Or is it different in project mode?
Andy
August 20, 2003 at 11:53 pm
Hi nulad,
some notes on this.
-for me this works. I'm on Access 2000 and when I link both tables Access maps the column as text. No difference in varchar or nvarchar.
-so when you are importing the data into your Access db it shouldn't be a problem to change the underlying data type after the import.
- must you really perform the JOIN on a Nvarchar-Varchar combination? Isn't there any numeric data
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 21, 2003 at 9:36 am
An Access project contains only code-based or HTML-based database objects: forms, reports, data access pages, macros, and modules. Unlike a Microsoft Access database, an Access project does not contain any data or data definition based objects: tables, views, database diagrams, or stored procedures. Instead, these database objects are stored in the SQL Server database.
I am unable to change the datatypes in the off-the-shelf program. SQL Server states the off-the-shelf program's column type as varchar 12. When you view the off-the-shelf program's column type in the Access Project, it states the column type as nvarchar (why I do not know). When I attempt to do any joins on the off-the-shelf column and the in-house program's column (the in-house column can be changed to varchar or nvarchar, it does not matter) it states they are incompatible data types.
Any idea why the Access Project does not see the off-the-self program's column as varchar, but SQL Server states that it is a varchar?
August 21, 2003 at 11:50 pm
Nulad,
quote:
An Access project contains only code-based or HTML-based database objects: forms, reports, data access pages, macros, and modules. Unlike a Microsoft Access database, an Access project does not contain any data or data definition based objects: tables, views, database diagrams, or stored procedures. Instead, these database objects are stored in the SQL Server database.
I must admit I've never worked with Access project before, but I've tried it right now and I see my tables, I see the NVarchar column and the Varchar column
and I am able to join them and get the expected results.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply