connect to database

[pbaldy]

Sorry, I didn't notice you had Number there. I'm not up on Oracle data types, but you probably want Integer or Long.

[newaccess]

I changed it to long. But I still do not see msgbox appears. no error message.
how to debug SQL return value in VBA codes?

some wrong?

I need some help in detail

Newaccess

[pbaldy]

Can you post the db, or a representative sample?

[newaccess]

in button click event

Private Sub Command2_Click()
Dim curDatabase As Object
Dim rstNWD As Object
'Dim comfirmNUM As Integer
Dim comfirmNUM As Long
'Dim comfirmNUM2 As Integer
Dim comfirmNUM2 As Long
Dim conConnector As ADODB.Connection
Dim strConnection As String
Dim strStatement As String

strStatement = "select max(INVOCLE_NUMBER) from SALES;"

Set curDatabase = CurrentDb
'Set tblNWD = curDatabase.TableDefs("SALES")
Set conConnector = New ADODB.Connection
conConnector.Open "DSN=xx_ODBC;UID=sales1;PWD=sales_rep;"
conConnector.Open strConnection
conConnector.Execute strStatement
'comfirmNUM = CLng(strStatement.INVOCLE_NUMBER)
comfirmNUM2 = DMax("INVOCLE_NUMBER", "SALES")

Forms!Form_Formtest!Text0 = comfirmNUM
MsgBox comfirmNUM2
MsgBox comfirmNUM
DoCmd.RunSQL "insert into SALES (SCODE, INVOCLE_NUMBER, TRANSACTION_DATE, COMMENTS) values (10697, '1111', '7/25/2009','test xxx');"

conConnector.Close
Set conConnector = Nothing
End Sub

in VBA codes, I used a oracle database table sales. in Access linkedtable name as test_sales.


Thanks for you help!
Newaccess

[pbaldy]

I'm not clear on whether you still have a question, or reporting success.

[newaccess]

codes does not works.
could you correct it?
Thanks very much!

[pbaldy]

I'm back to:

Can you post the db?

[newaccess]

MS Access 2007 in window 7.
No access tables. ONLY linktable with ODBC to Oracle database
Actually, I want to create a data enter form and put data into oracle database. the oracle table already is linked into Access table.