ODBC Access Update

[OptimisticYid]

Used to use these forums quite regularly and always found people to be of help, even started chipping in occasionally with the odd pearl myself

Anyhow, having forgotten my username i'm back under a different guise and have a problem that i'd like some help with.

My company has a website running asp (not .NET) with an sql database backend. Basically we want a localised system to deal with order processing with daily updates to customer, order tables etc. I started to build something in VB and SQL but time is critical and once I work out this problem I can churn something out in access double quick time.

I can get the ODBC connection open to the sql database and import the data to Access 2007 table (ie dbo_tblOrders), but I then need to find/write some code to fetch extra records daily and append them to the EXISTING tables. At present data is fetched and added to a new table. Is it merge queries I need? If so how, and can I set it to only fetch new records?

 

[KenHigg]

To start with I would attach to the table instead of importing it.

 

[namliam]

Yep Ken is right.... Use linked tables instead of imported ones.
This way you can use the computing power of the server rather than your own machine.

There are otherways for doing incremental updates, but.... Linking in this case seems optimal

 

[OptimisticYid]

To start with I would attach to the table instead of importing it.

If i create linked tables rather than importing it recognises the structure fine but shows all data as #DELETED ....?

 

[KenHigg]

My first guess is a permission somewhere has you locked up. Do you have access to the dba?

 

[OptimisticYid]

Reckon so, using the same ODBC connection as I did for the import (which worked fine) do i need to change permissions server side? Bear in mind I can only access our part of the whole sql server, which i do through server management studio express.

 

[KenHigg]

I would suggest creating a simple table with default permissions and put a few rows of data in it and the try to link to it.

 

[OptimisticYid]

I have added a test table with a bigint ID field and a varchar data field. When I add the table it tells me that I may not be able to save changes as I am not the database owner or system administrator (bearing in mind we rent a part of a sql server Stateside, the cost of having our own is prohibitive) however it still allows me to save the schema. I have created a linked table in my Access database to this table and still receive all data as #DELETED (imported table works fine as before), I'm lost

 

[Rabbie]

Remember Access does not work well over a WAN.

 

[OptimisticYid]

Remember Access does not work well over a WAN.

Och aye, but i kind of hoped that Mr Gates would put out some software with features that worked properly. How naive am I?

 

[KenHigg]

I would email the dba and ask for his assistance -

 

[Rabbie]

Och aye, but i kind of hoped that Mr Gates would put out some software with features that worked properly. How naive am I?

This thread has some onformation you may find useful