Updating a table in a linked database 1

[nimarii]

I need to be able to create/update records in a linked database, and I have no idea where to start. Here's the situation:

I have a database (database A) that has a table (Orders) which lists sales orders. I import data into this table on a daily basis. The second database (database B) has a table called Payments, and also has a linked table from database A (the Orders table). What I need to do is that everytime I import sales records into database A, I need to create/update a record in database B in the Payments table.
I also need to update the records in the Payments table whenever a record in the Orders table (database A) changes its status (from New to Pending to Cancelled, Completed, etc).

I hope this isn't too confusing...can anyone please advise?

Thanks in advance!

 

[nimarii]

I don't know too much about ODBC, but can anyone tell me if its possible to create an ODBC connection from one access database to another access database, and update the 2nd database from using vba in the first one, without ever having to open the 2nd one?

if so, can someone advise me on how to go about doing that?

any other suggestions are more than welcome...

 

[CajunCenturion]

Can you link to the Payments table in Database B from Database A?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[nimarii]

thanks for the reply!
unfortunately no - the specifications I've received for this are pretty rigid....

 

[CajunCenturion]

Then I would try to establish an ADO connection to the other database, and issue the appropriate SQL command through the ADO Connection and/or Recordset Objects.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[nimarii]

Thanks, will definitely try to do that, although...can you tell me how to set up an ADO connection?

thanks so much!

 

[KarMac21]

Hi Nimarri - Try this out

Public Sub UpdateTables()
Dim cn as New ADODB.Connection

cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\\yourprojectpath\Database B.mdb"

'To update a record run this
cn.execute "UPDATE Payments Set Field1=Value1, Field2=Value2 Where Somefield = somevalue"

'To Add a record run this
cn.execute "INSERT INTO Payments (Field1,Field2) Values (Value1,Value2)"

cn.close

End Sub

Hope this helps,

Shane

 

[nimarii]

Thanks KarMac21, this worked perfectly!

 

[nimarii]

Hi KarMac21, sorry to open this issue again, but although i've established a connection, it doesn't seem to be able to recognize the table names in the database i just connected to.

What I'm trying to do is connect to the remote database, then pull one of the tables from the database into a recordset to use in my code. When i try to create the recordset, i get an error saying it doesn't recognize that input table or query...

here's the code i'm using:

Private Sub UpdateLinkedTable_Click()
Dim cn As New ADODB.Connection
cn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Documents and Settings\elee\Desktop\db1.mdb"
strSQL = "SELECT * FROM t_paymentamounts WHERE callingplan = " & LDplan
    Set PaymentAmounts = db.OpenRecordset(strSQL)

I can't seem to get past this point in my code. Do you have any idea what I'm doing wrong?

 

[SkipVought]

Check your SQL String -- need tics and quotes

strSQL = "SELECT * FROM t_paymentamounts WHERE callingplan = '" & LDplan & "' Set PaymentAmounts = db.OpenRecordset(strSQL) "

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RoyVidar]

Openrecordset is a dao method, Connection is an ado object, you can't mix them. Here you're trying to open a recordset in the current database, because db doens't know about the opened connection.

KarMac21 is demonstrating the .Execute method of the ado connection object.

Check out the helpfiles on ADO recordsets (available thru F1, and also the file(s) ado*.chm somewhere on the harddisk), sample of opening an ado recordset could be:

[tt]dim rs as adodb.recordset
' declare and open your connection, assign the sql string
set rs=new adodb.recordset
with rs
.activeconnection=cn
.cursorlocation=aduseclient
.cursortype=adopenkeyset
.locktype=adlockoptimistic
.open strSQL,,,,adcmdtext
' useful code
.close
end with
set rs=nothing
cn.close
set cn=nothing[/tt]

Roy-Vidar

 

[nimarii]

Thanks Roy-Vidar, you've saved me ALOT of time and trouble! (thanks also to skipvought, turns out there was an issue with SQL statement that was preventing the recordset from opening correctly).

one more thing i'm having a problem with, seems that my Insert statement is incorrect:

cn.Execute ("INSERT INTO t_payments (paymentreference, customerid, paymentbtn, paymentordernumber, PaymentOrderDate, paymentamount, paymenttype) VALUES('" & PayRef & "', '" & custID & "', '" & BTN & "', '" & orderNum & "', '" & SaleDate & "', '" & Payment & "', '" & paymentType & "'")

all the values shown are variables I created in the code that hold differing values. (some are from the DAO recordset (Created before the adodb connection in the current project), some are from the ADO recordsets, and some are just text.

what am i doing wrong....?

 

[RoyVidar]

Only suggestions I have:
* seems to be a missing closing parens of the value clause, should probably be [tt]...& "')")[/tt]
* not sure, but I always put a space between "Values" and the opening parens
* single quotes only for strings, numerics no delimiter, dates in access hash (#) sql server single quotes

so judging by the names of the variables/fields one suggestion could be:

[tt]cn.Execute ("INSERT INTO t_payments (paymentreference, customerid, paymentbtn, paymentordernumber, PaymentOrderDate, paymentamount, paymenttype) VALUES ('" & PayRef & "', " & custID & ", '" & BTN & "', " & orderNum & ", #" & SaleDate & "#, " & Payment & ", '" & paymentType & "')")[/tt]

Sometimes it is helpfull seing the errormessage;-)

Roy-Vidar