asked on

How do I connect to a ODBC database and update a record from another database

I have an Access data base called TCR with tblMain ,( field Job). We have another database ODBC sql server called JobBoss. There is a field in the JobBoss database (table Job) called Status, There is also a field called Job. What I need to do is when a person checks a checkbox in dbase TCR I need the field in dbase JobBoss to be updated from “Active” to “Hold”, based on the job number in table TRC.

So far I have the following but I’m not sure how to put it all together.

cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "uid=S;pwd=;server=exserv;driver={ODBC 3.51 Driver};Database= JobBoss32;dsn=;"
Set ActiveConnection = cnMySql
cnMySql.EstablishConnection
With rdoqry
.SQL = "Update Job Set Status= Hold WHERE JOB= JobTest"

rdoqry.Execute
cnMySql.Close

ASKER CERTIFIED SOLUTION

Dale Fye

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

I'll try and let you know

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

Yes I have that correct, it appears that I'm not connecting to the other database correctly to update the field. This is driving me crazy because i know it is something small in my connection code that is wrong.

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

M/S sql server

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Nick67

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Nick67

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

The database Im trying to connect to is our production database. The users of this other database are not computer savy at all. I need them to be able to click a button, and the production table will update 1 field from Active to Hold so that a job on the production floor goes no further. Since Ive contacted you, I think I have been able to connect via the database that I am building. I'm writing the update query now, which also isnt going that great. The connect string that I have is
cnMySql.Connect = "Provider=SQLOLEDB;Data Source=Exserv;Initial Catalog=production;User ID=support;Pwd=lonestar;"
Set ActiveConnection = cnMySql
cnMySql.EstablishConnection
The only thing it opens a window where they have to select the Joboss database. That will be a problem. Then it appears that it connects.

SOLUTION

Nick67

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

Okay, I am connecting to the database how ever I cant update the table Job (field) Status .
the code Im using is
With rdoqry
.SQL = "UPDATE Job SET Job.Status = 'Hold' WHERE Job = 'JobTEST'"
Set ActiveConnection = cnMySql

Any Ideas?? Do I need to Open a recordset set????

SOLUTION

Eric Sherman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Spikemom

ASKER

To all who tried to help me with this problem

Thank you.
I still havent been able to access the database and update a field and have given up.

Thank you for all your help and suggestions anyway
Spikes Mom