Link to home
Start Free TrialLog in
Avatar of emmac83
emmac83

asked on

Update a field in a table in SQL Server 2005 from a button in Microsoft Access 2000

I have an access database and a SQL server database.  I want to be able to update a field in a table in SQL Server from  a button in Microsoft Access.

Can anyone provide the code to do this.

I need the code to include what database name to connect to and a username and password.

Thanks
Avatar of Aneesh
Aneesh
Flag of Canada image

Hello emmac83,

You  need to create a linked server b/w these two and can update

UPDATE T
set sqlField = a.AccessField
FROM SqlTable t
INNER JOIN  [linkedServerName]..[TableName] a On a.Column = T.Column

Regards,

aneeshattingal
You can use VBA code to do it....just create an ADO connection and execute the update statement.  You'll need a connection string to the sql machine.

www.connectionstrings.com

How to do it...
http://forums.databasejournal.com/showthread.php?t=40892
Avatar of emmac83
emmac83

ASKER

chapmandew, can you give more detail on creating the ADO connection, Im not very familiar with VBA.

Thanks
Sure....
 Dim objCon As ADODB.Connection
    Dim objCom As ADODB.Command
    Dim objPara As ADODB.Parameter
    Dim objpara2 As ADODB.Parameter
    Dim objRS As ADODB.Recordset
    Dim k As Integer
    Dim strdbpath As String
    Set objCon = New ADODB.Connection
    Set objCom = New ADODB.Command
   
    '...Creating the DB connection string as per your server and database being used.
 
    objCon.ConnectionString = "Driver={SQL Native Client};Server=     (local);Database=mydb;Uid=username;Pwd=asdfasdfasdf;"
    'Opening the connection
    objCon.Open objCon.ConnectionString
    'assigning the command object parameters
    With objCom
        .CommandText = "usp_TestProcedure"     'Name of the stored procedure
        .CommandType = adCmdStoredProc  'Type : stored procedure
        .ActiveConnection = objCon
    End With
   
    'Create 2 output parameters
    Set objPara = objCom.CreateParameter("@Param1", adInteger, adParamInput)
    Set objpara2 = objCom.CreateParameter("@Param2", adVarChar, adParamInput, 5)
    'Append the output parameters to command object
    objCom.Parameters.Append objPara
    objCom.Parameters.Append objpara2
    objPara.Value = 5
    objpara2.Value = "tim"

Open in new window

Avatar of emmac83

ASKER

i understand the first bit but I dont get the 2nd part with the output parameters.

I just want to update a bit field called despexport to 'true' depending on a date entered and status = 'ACTIVE'.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of emmac83

ASKER

Thats great, almost there.

The field despexport is in a table called salesorderitem_custom and the date and status in a table called salesorderitem.  They are joined via a field called orderid.

Could you amend the update statement to allow for this?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of emmac83

ASKER

thanks