SQL ODBC DTS

[sparkyrose]

Hi,

I'm fairly new to SQL Server, so apologies if this is a no-brainer.

I have a SQL Server Database, with Access front end, linked through an ODBC connection. (call this "My Db")

I wanted to link a table from another Db (which is on the same server as my Db) directly to Access but can't because the primary key of the table I want to link is of the Bigint data type which Access can't handle.

I tried a CREATE VIEW statement in the other Db, using [CAST (column_name as "float")] but with no success. I know Access can handle 'float' because that's what My PK is.

Basically, I need some way to maintain a dynamic 'copy' of the table in My Db, in which I can change the data type.

Creating a copy in My Db was no problem (I used the DTS Wizard) but I can't think/work out how to make it a dynamic copy that Access can link to.

As I say, sorry if this is obvious. If someone can at least point me in the right direction I can probably work out the details....

 

[SQLDenis]

why not convert to int instead of float?
What if you have real bigints in there (greater than 2 billion an change)?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[sparkyrose]

Thanks for the reply.

I have tried Int too but with no success. Is there another function other than CAST that would be better?