connect to database

[newaccess]

Hi Experts,

I set up an ODBC and created a linktable in access2007 from oracle database.

I created form based on linktable and try to code a column click event.

The code as
Private Sub CONFIRMATION_NUMBER_Click()
Dim curDatabase As Object
Dim rstNWD As Object
Dim INVNUM As Integer
Dim conConnector As ADODB.Connection


Dim strConnection As String
Dim strStatement As String

strStatement = "select max(invoce_NUMBER) from sale_info;"

Set curDatabase = CurrentDb
Set tblNWD = curDatabase.TableDefs("SALES")
'Set rstNWD = curDatabase.OpenRecordset("SALES")
Set curDatabase = Application.CurrentProject
' or this option
Set conConnector = New ADODB.Connection
conConnector.Open "DSN=sales_ODBC;UID=SALE2;PWD=sale_rep;"
conConnector.Open strConnection
conConnector.Execute strStatement

MsgBox strStatement.INVNUM


Set rstNWD = Nothing
Set curDatabase = Nothing

End Sub


I got a compile error as user-defined type not defined. debug stop at Dim conConnector As ADODB.Connection
Actually, I already set up a oracle ODBC connection and works. Access is able to select all data from oracle database.

Please help me how to program to access database by ODBC

Thanks
newaccess

[pbaldy]

In Tools/References you need to check the MS ActiveX Data Objects 2.x Library.

[newaccess]

Hi Paual,

I can not to find Tools/References menu at MS access 2007?

May I have detail steps?

Thanks
newaccess

[pbaldy]

In the VBA editor, not Access itself.

[newaccess]

Thanks for your help!
It works. But I got a compile error: invalid qualifier at strStatement.INVOCE_NUMBER = InvolceNUM

my sql only selects one column number invoice_number. the oracle column type is number. but I could not declare as Dim InvolceNUM As number.

How to fix it?

Newaccess

------------------
tho entire code as below

Dim curDatabase As Object
Dim rstNWD As Object
Dim InvolceNUM As Integer
Dim conConnector As ADODB.Connection
Dim strConnection As String
Dim strStatement As String

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

Set curDatabase = CurrentDb
Set conConnector = New ADODB.Connection
conConnector.Open "DSN=NWD_ODBC;UID=sale1;PWD=sale_rep;"
conConnector.Open strConnection
conConnector.Execute strStatement

strStatement.INVOCE_NUMBER = InvolceNUM

[pbaldy]

What are you trying to accomplish? strStatement is a string variable, so it has no methods or properties you can set (no ".INVOCE_NUMBER"). You probably want to open a recordset on that SQL rather than executing it.

[newaccess]

I try to get a return number from sql--max(INVOCE_NUMBER) and display it.

How to do that? may I have some condes. I am new person for Access.

Thanks for you help.

Newaccess

[pbaldy]

If the table is linked you might find a DMax() simpler:

DMax("INVOCE_NUMBER", "SALES")

[newaccess]

I declare comfirmNUM and assign as
comfirmNUM=DMax("INVOCE_NUMBER", "SALES")

MsgBox comfirmNUM2

but there are no msgbox display value

Some wrong?

[pbaldy]

Sure; the variable in the message box has a "2" in it, the one being given the value does not.

[newaccess]

sorry for my mistaken.
comfirmNUM2=DMax("INVOCE_NUMBER", "SALES")

MsgBox comfirmNUM2


no msg box appears

[pbaldy]

Where is the code? Are you sure it's running? No message box at all would indicate it's not. What's the full code for that event?

[newaccess]

I created a new form with a text box and a command button.
on button clicked event with below code
Private Sub Command2_Click()
Dim curDatabase As Object
Dim rstNWD As Object
'Dim comfirmNUM As Integer
Dim comfirmNUM As Number
Dim comfirmNUM2 As Number
Dim conConnector As ADODB.Connection
Dim strConnection As String
Dim strStatement As String

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

Set curDatabase = CurrentDb
'Set tblNWD = curDatabase.TableDefs("SALES")
Set conConnector = New ADODB.Connection
conConnector.Open "DSN=xxx;UID=xxx;PWD=xxx;"
conConnector.Open strConnection
conConnector.Execute strStatement
comfirmNUM = strStatement.CONFIRMATION_NUMBER
comfirmNUM2 = DMax("INVOCE_NUMBER", "SALES")

Forms!formtest!Text0 = confirmNUM2
MsgBox comfirmNUM2
End Sub

I want to get a value from sql and assigns return value to text box.
I added two way to get value in VBA codes. But I got error message as
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of
the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs
because Access expects a macro or event name to run when the event is fired.


Please correct my code.

Thanks
Newaccess

[pbaldy]

Again assuming that the table is linked, the only lines you need are

Dim comfirmNUM2 As Number
comfirmNUM2 = DMax("INVOCE_NUMBER", "SALES")
Forms!formtest!Text0 = confirmNUM2

I'm not clear on the part about the On Open property of a form being =[Field]. That wouldn't be valid in the properties window.

[newaccess]

I try to debug.
the Dim comfirmNUM As Integer is OK But
Dim comfirmNUM As Number get compile error.--user-defined type not defined

for file value onlin syntax as
=Forms!FormName!FieldName

Thanks for help!
Newaccess