Excel ODBC Direct Connection to SQL Recordset

[DanMan1]

Hi,

I'm trying to connect to an SQl recordset and update some columns I can connect to the server and return a recordset but I can't update it as its read only any ideas on how to get it read write?

Heres my code:

To connect:
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conHolODBC = wrkODBC.OpenConnection("CMSCon", , False, "ODBC;DSN=Holiday;Description=Holiday;APP=Microsoft® Access;WSID=" & Environ$("computername") & ";DATABASE=Annual Leave;Trusted_Connection=Yes")

to return recordset:
strSQL = "SELECT * FROM AGENT_AVAILABILITY WHERE VACATION_YEAR=" & strDate & " AND AGENT_ID=" & lngAgentID
Set rs = conHolODBC.OpenRecordset(strSQL, dbOpenDynaset)

Any ideas would be appreaciated.

Thanks

 

[xlbo]

If you are trying to update tables on a database, it will be permissions on those tables that you need to change

AFAIK, ADO and ODBC have read / write functionality - you just have to write the appropriate SQL and have the appropriate permissions on the tables you want to update...

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

 

[DanMan1]

Hi Geoff,

For some reason it still doesnt work and tghe permissions are set. If I conect to an access db and then update via a linked table it updates fine.

Cheers

Dan

 

[xlbo]

How are you trying to update the table ?

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

 

[DanMan1]

Using the below:

With rs
.Edit
If blnbook = True Then
!REMAINING = !REMAINING - (lngBookMins / 60)
!Selected = !Selected + (lngBookMins / 60)
Else:
!REMAINING = !REMAINING + (lngBookMins / 60)
!Selected = !Selected - (lngBookMins / 60)
.Update
End If
End With

Any Ideas?

 

[PHV]

Just a note: why updating only in the Else part ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DanMan1]

Good point, school boy error. Thanks.

It could be due to the default setting for ODBC direct connections is readonly.

Thanks for your advise Geoff i'll have to play around with it.