Accessing Oracle tables from Microsoft Access 2

[Glohamar]

I am a real new to the Oracle DBMS and I am having trouble creating queries, or SQL statements. I can build the queries using the Access Query views, but I am needing to be able to write SQL type statements as my application will be going to 20+ users and will be accessing the Oracle DB from VBA code.

I have seen some code that accesses Oracle and do not understand how to set up my "SELECT value FROM tablename WHERE value = " statement. Some code I have seen I see that there are letters like a.value and b.value. I do not understand.

Just need some direction for a jump start.

Thanks for any help provided.

Dave

 

[BJCooperIT]

I believe what you are referring to is an alias. Think of an alias as a nickname for a table or column. Aliases can be used as a shorthand (for lazy typers like me), or for clarification. In the example below, the column NAME must be prefixed by the table name or an alias to prevent an ambiguous column reference.

select employee.empno,
       employee.name,
       department.name, 
       (hourly_rate * 40)
from   employee, 
       department
where  employee.deptno = department.deptno
order by employee.deptno, employee.empno

The following SQL assigns EMP as an alias to the EMPLOYEE table, DEPT as an alias to the DEPARTMENT table, and column aliases to prevent two columns called "NAME", and to identify a calculated value.

select emp.empno,
       emp.name           [COLOR=red]employee_name[/color],
       dept.name          [COLOR=red]department_name[/color], 
       (hourly_rate * 40) [COLOR=red]weekly_pay[/color]
from   employee [COLOR=red]emp[/color], 
       department [COLOR=red]dept[/color]
where  emp.deptno = dept.deptno
order by emp.deptno, emp.empno

Hope this helps.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[Glohamar]

Ok, I am starting to understand. Thanks for the info. So, if I am thinking right, I can write SQL statements and everything should be ok, or are the aliases a requirement? Right now I have a SQL statement taking a field value and I am wanting to look it up in the Oracle tables. Here is my SQL statement:

strSQL = "SELECT EC_NUMBER, TITLE, PRIME_MODEL, PRIORITY FROM CCM_EC " & _
         "WHERE CCM_EC.EC_NUMBER = " & Me.ECNum

I get data type mismatch. The table I am querying is CCM_EC. Do you see anything wrong with what I have. I have used this when using SQL Server and it works fine.

ALso, any suggestions for web sites that would help me as a beginner.

Thanks

 

[KenCunningham]

Aliases aren't a requirement in single-table queries, but act as an aid to decipering what's happening as well as being required if the column names are the same in both/all tables in a join query.

 

[KenCunningham]

Sorry, hit 'post' too soon. Think of it like the name John_Smith. There are plenty around, but an identifier like London.John_Smith and Cardiff.John_Smith indicates exactly where the data is drawn from., assuming you have your aliases set accordingly.

 

[Turkbear]

Hi,
In your Sql Statement be sure that Me.ECNum
is a Number and not a alphanumeric string.

If you can display the contents of strSql after setting it, check to see what exactly is being passed to Oracle.

 

[Glohamar]

KenCunningham, thanks for your explanation, that helps.

Turkbear, Me.ECNum = "054335" when I am in debug mode and the field in the Oracle database is set up the same way. I have ECNum as an Integer. I am going to post all of my code because I am wondering if there is something else that might be causing my error. I have changed the way I am retrieving Me.ECNum to see if it made a difference, but it did not matter, still get Type mismatch.

Dim ors As ADODB.Recordset
Dim db As Database
Dim strSQL As String
Dim ECNum As Integer


Set db = CurrentDb()

strSQL = "SELECT EC_NUMBER, TITLE, PRIME_MODEL, PRIORITY FROM CCM_EC " & _
         "WHERE CCM_EC.EC_NUMBER = ' " & Me.ECNum & " ';"
         
Set ors = db.OpenRecordset(strSQL)

Also the tables are linked, but will be used through a connection string on my released application
Thanks for your input everyone, I really appreciate all of your help.

Dave

 

[Turkbear]

Hi,
So, if I understand you,
CCM_EC.EC_NUMBER is an Varchar2 data type.

Perhaps you need to use CStr(Me.ECNum), since you have defined it as an Integer and are sending it as a string.

 

[Glohamar]

Actually, EC_NUMBER type is CHAR(6). Should I make my ECNum a string? I guessed that CHAR would be integer, guess I was wrong.

Thanks for your help Turkbear.

Dave

 

[Turkbear]

Hi,
Unless you cannot avoid it, do not use CHAR as an Oracle datatype, it causes problems when comparing values due to its blank-padding behavior - Convert the field to a VARCHAR2 type..

Yes, make ECNum a string - either create it that way or convert it before use like this, for instance:

"WHERE CCM_EC.EC_NUMBER = '" & Me.ECNum & "'"

Hope it helps...



PS, why the Me. prefix to ECNum , is it from some other table or recordset?

 

[Glohamar]

Thanks for your help Turkbear, really appreciate everything.

I am not able to make changes to the Oracle tables as they are already established and being used by another application, I am just needing some of the info that they store so I am not re-typing the stuff.

The Me. refers to the form I am working with. So instead of Forms!frmMain!ECNum I can use Me.ECNum and I get the same result.

Thanks for everything.
Dave

 

[Turkbear]

Hi,
No problem, glad to help..