Excel-ODBC Connection with SQL database

[gk759]

Hi,

I am in the process of linking Excel spreadsheet with a SQL database using Connection manger. I have few concerns about this:

1) Is the ODBC connection link a two way connection?
2) If yes, if I update or change any field will it overwrite the information in the SQL databse?

Looking forward to your reply,

Thanks,

Gary

 

[xlbo]

Depends on your permissions. ODBC is a connection - it is not necessarily read only. Everything depends on your table / database permissions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

UPDATE (change) implies that you are CHANGING one or more rows in a table, based on the criteria keys and the fields containing new data.

INSERT (add) puts in new rows of data.

Skip,

_[red][/red]

 

[SkipVought]

... and, for that matter DELETE, removes rows from the table. ALL these can be accomplished.

Skip,

_[red][/red]

 

[gk759]

Hi,


I am of the understanding that when I use excel connection manager, the path is mapped to the SQL server tables using OLE DB. Once the table is selected, excel creates a copy of the table on the worksheet. This table is independednt and doesnt have a live connection with the SQL server databse. I may have confused you guys by stating ODBC connection in my earlier message. Please confirm now if I am correct in assuming the above about the connection manager using OLE db.


Cheers,

Gary

 

[xlbo]

It all depends on your query type. If your query is a SELECT query then it will return data to excel. It is possible through code (don't think it is possible without code) to update a table in SQL server using any kind of connection - ODBC / OLE DB - doesn't really matter.

It sounds like in your situation that you are using the MS Query GUI to return data to excel. This is fine and cannot overwrite the SQL Server table. In terms of security however, if the connection is there and someone knows how to utilise it they could do so to enable them to write to the database - if they have permissions to do so. As per my 1st post - the only way someone can use a connection to update a database table is if they have database permissions to do so

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question