Linking SQL Union View

[aron.ridgway]

Hi there i have a Union SQL view for an external data source, when its imported into access all the data types revert to Short text and i have no primary key.

I understand you cannot edit linked tables but any ideas how i get firstly the SQL view to import with the correct data types and secondly set a primary key?

I have attached a photo of the sql view and it has bignit for the AccountID which i need.

Is there a way of changing them using VBA?

thanks

 

[namliam]

its pretty simple just create the table yourself in access then you dont need to worry about the conversion. BigInt simply doesnt exist in Access, thus access does its best to convert it....

Unions simply cant have PK despite the source of the table, exactly the same solution simply have a table container (with PK) waiting for your data.

 

[aron.ridgway]

Hi thank you for your reply, sounds like a perfect solution.

Im pretty new to access how would i go about setting it up?

Set up the data fields? But i need the linked information to refresh also?

thanks

 

[namliam]

Well simply import say top 10 into a local table.
Then you have the basic structure in place, but you then alter the structure, keys and what not to what you need it to be.

Then if you need an update, delete the data from your local table. Append data from your linked union... simple running of two queries which you can very easily automate as well if you pref.

 

[aron.ridgway]

If i delete the data and say 3 new items have been updated. i may get the issue that the primary key ID numbers have changed which will effect data already saved in other tables?

 

[namliam]

No because your PK comes from your UNION view doesnt it?

 

[aron.ridgway]

nope it dosnt which is the main issue, as some of the Account Id's conflict

 

[namliam]

is there anyway to make it unique, i.e. using accountnumber?

 

[aron.ridgway]

Yep i've managed to make it Unique now by adding RWL or RFW at the start of the code.

Ive created a query on the new table, and now managed to set a Primary! If i want to refresh do i need to delete the table and re import via VBA?

if so does that mean i have to set the primary key for the table each time?

thanks

 

[namliam]

Yes, that is unless you can automagicaly know which is which...