Using a DSNLess connection to an Oracle Dataview and access the data w/o using temp table.

Sorry but I did not know what "(Host=o"") should be, so I just dropped the oh...

the quotes were only used to hide the actual data - my connection string work fine it is the viewing of the data, not the connection string.

k

and type mismatch issues between the Oracle datatypes and Access's datatypes.

is there a way to work around this issue?

k

as you see in my code - is there a way to actual view the dataview from Oracle using the DSN_less connecton string and have the the view's data open for use.

k

Not knowing that you mangled the connection string, I figured it was a good place to start.

I know of no reason whay you cannot view data from an Oracle view in Access. Perhaps try a pass-through query?

Not sure how to use a pass thru when using a DSN_Less connection string.

k

When you say "view the data", what are you using to view it? If this is a Form, you can build your ADO recordset and set your form to that recordset like this:

Set Me.Recordset = YourRecordsetObject

Assuming your have controls on that form that correspond to the fields in the recordset, you'd see your data.

Nevertheless, if you want to use the code as you supplied, try specifying the order of your columns in the rs recordset, instead of using SELECT *:

strSQL = "Select Col1,Col2,Col3 etc from tblFTIR_vw"
        rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

Another issue is the data type conversion between oracle and Access, has displayed in my code I tried setting the field values via a loop statement.

but ran into the data type conversion issue.

What about make table type of code to set the field names and field datatypes, values, etc.  Know of any code that might do that.

k

Will this statement actual open a query and display the results?

strSQL = "Select Col1,Col2,Col3 etc from tblFTIR_vw"
        rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

Just thought should this could be placed in the SQL pass thru query wizard or in VBA?

k

As you can see, I also tried this type of connection string. Still not sure how to view the actual data once the connection is made?

 Public Sub ConnectFTIR()
  
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim lCnt As Long
    Dim sSQL, strSQL As String
    Dim rstOra As ADODB.Recordset
    Dim rsAccess As New ADODB.Recordset, rs As New ADODB.Recordset
    Dim fld As ADODB.Field
    
    On Error GoTo ErrorHandler
        
    DoCmd.SetWarnings False
     sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
            "(Host="")(Port="")))(CONNECT_DATA=(SID=TEST1)));" & _
            "User Id=Test1;Password="";"
    CurrentDb.Execute ("Delete * from tblFTIR_VW")
    
    strSQL = "INSERT INTO tblFTIR_VW ( FTIR_MeasNo, FTIR_ApNo, FTIR_Title, FTIR_RM, FTIR_SPS, FTIR_Min, FTIR_Max," & _
                " FTIR_Units, FTIR_Acc, FTIR_Type, FTIR_Loc, FTIR_Fltr, FTIR_HCO, FTIR_LCO, FTIR_BusName, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SpHdl, FTIR_Desc, FTIR_MaintCd, FTIR_Cmt, FTIR_InstlDwgNo, DtAppnMeasNo, DtUpdtMeasNo )" & _
            " SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE" & _
            " From Test1.Table1" & _
            " WHERE Test1.Table1.FTIR_APNO = '" & gAPNo & "'"
    
    adoRS.Open strSQL, adoConn, adOpenStatic, adLockPessimistic
    Set rstOra = New ADODB.Recordset
 
        rstOra.ActiveConnection = sConn
        rstOra.CursorType = adOpenKeyset
        rstOra.LockType = adLockOptimistic
        rstOra.CursorLocation = adUseServer 'default
        rstOra.Open "FTCSVERF.FTEM_VI_VW"
 
        Do While rsAccess.EOF = False
            rstOra.AddNew
            On Error Resume Next
                For Each fld In rsAccess.Fields
                    rstOra(fld.Name).Value = fld.Value
                Next
            rstOra.Update
        rsAccess.MoveNext
        Loop
        rstOra.Close
        Set rstOra = Nothing
        oConn.Close
        Set oConn = Nothing
 
SubExit:
    On Error Resume Next
    If Not oConn Is Nothing Then
        Set oConn = Nothing
    End If
    rsAccess.Close
    Set rsAccess = Nothing
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConnectFTIR of Module ModConnectors"
    Resume SubExit
 
End Sub
 
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:

<Will this statement actual open a query and display the results?>

No ... this opens a recordset, which is sort of like an "in memory". Once you open a Recordset, you would then do something with it (like loop through it and set a value in one of the fields, or perhaps use it to manipulate data in another recordset). In your case, if you want to see it in something like a query or datasheet view, you'd probably be better off dumping it to a temporary table.

As to datatype conversions ... this can be difficult, since Oracle (or any other server-type database) will have datatypes which aren't compatible with Access.  It appears that Oracle BLOB/CLOB/NCLOB/NCHAR columns can cause problems coming into Access; I'm not sure of the workaround for them.

You could also build a form that would show this and set the Form's Recordset to the ADO Recordset you create. Just build your form (add the textboxes and set their Tag property to "d"; don't bind them), then open your Oracle recordset. Bind the controls on your form like this:

Dim rst As ADODB.Recordset
<build your connection and ADO Recordset>
Dim i As integer
Dim j As Integer

For i = 0 To Me.Controls.Count -1
  If Me.Controls(i).Tag = "d" Then
    Me.Controls(i).ControlSource = rst.Fields(j).Name
    j = j +1
  End If
Next i

Set Me.RecordSet = rst

Assuming you have the same number of controls as you do Recordset fields, this would work. Set the Form to Continuous or Datasheet view and you'll see all the values in that recordset.

If I was to use Maketable based on the Oracle data and data types would this solve the problem of datatype issues?

k

here is my latest attempt to create a table on the fly with the Oracle data.  I am getting
Object variable or With block variable not set (Error 91) error - don't see what I am missing - need an extra pair of eyes.  see'******

k

 Public Function FTIRConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection
 
    sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
             "(Host=Test)(Port=1521)))(CONNECT_DATA=(SID=ftcsverf)));" & _
            "User Id=tst;Password=test;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    If IsTableExist("tblFTIR_vw") Then
        CurrentDb.Execute ("drop table tblFTIR_vw")
    Else
        strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User], Now() AS DateCreated" & _
                " INTO tblFTIR_vw" & _
                " FROM FTCSVERF.FTIR_DNLD_VI_VW"
'*****
        adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
    End If
    DoCmd.OpenTable "tblFTIR_vw"
        adoRS.Close
        Set adoRS = Nothing
        adoConn.Close
        Set adoConn = Nothing
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

You have to set adoRS to New:

Set adoRS = New ADODB.Recordset
adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly

Morning,

Thanks for the assist, however, I am still getting an error:  see '***********

Ora-00936 - Missing Expression

Here is my latest code

Karen

Public Function FTIRConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
 
    If IsTableExist("tblFTIR_vw") Then
        CurrentDb.Execute ("drop table tblFTIR_vw")
    Else
    sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
             "(Host=Test)(Port=1521)))(CONNECT_DATA=(SID=ftcsverf)));" & _
            "User Id=test;Password=test;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
 
        strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User], Now() AS DateCreated" & _
                " INTO tblFTIR_vw" & _
                " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
                " WHERE FTIR_APNO= " & Chr(39) & UCase(gAPNo) & Chr(39) & ""
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly'********
End If
    DoCmd.OpenTable "tblFTIR_vw"
        adoRS.Close
        Set adoRS = Nothing
        adoConn.Close
        Set adoConn = Nothing
        oConn.Close
        Set oConn = Nothing
 End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

Running a SELECT INTO query like this would build the table on the ORACLE db and not on the Access db (since you've connected to the Oracle db via the adoConn object).

I'm not sure of the error, however you have 2 DateCreated fields in this query ... also, do away with the Environ() call, and use Single Quotes if FTIR_APNO is a Text value:

Dim sUser As String

sUser = Environ("UserName")
strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE, Date as DateCreated," & sUserName & "AS [User], Now() AS DateCreated" & _
                " INTO tblFTIR_vw" & _
                " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
                " WHERE FTIR_APNO='" & UCase(gAPNo) & "'"

Thanks, however, do I need to change something to build the table in Access from the data collected from Oracle?

k

still getting same error

k

Found this on the web - not sure how it applies to my code.  Maybe the syntax for Oracle to create a table is different from Access.

ORA-00936:      missing expression

Cause:      A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action:      Check the statement syntax and specify the missing component.

Now I trying to execute the oracle portions separately, however, How do i incorporate these results within the Access sql

       strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                    " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                    " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                    " FTIR_LAST_REVISION_DATE" & _
                    " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
                    " WHERE FTIR_APNO='ZA001'" '" & UCase(gAPNo) & "'"
    Set adoRS = New ADODB.Recordset
    adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
        CurrentDb.Execute "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE, Date as DateCreated, Environ$('Username')AS [User]" & _
                " INTO tblFTIR_vw" & _
                " FROM (SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _

JUST WANT TO VIEW THE DATA FROM ORACLE FROM WITHIN ACCESS WITHOUT CREATING A FORM OR TABLE IS THAT POSSIBLE????????

K

No need to shout ... sorry you're having troubles, but it ain't my fault.

Can you create a linked table? If so, you could use it just as you would a local table. Otherwise, in order to view the data you'd need to create some form of "physical" object (form, report, etc) that would enable you to view the data onscreen.

If you must use a DSN-less connection, then you'll have to create a table of some sort. You could open your recordset to the Oracle db, then just loop through and use INSERT INTO to push the data into a local table. You'd have to build the table first, of course, but assuming you have that table built:

<open your recordset>

Do until rst.EOF
  Currentproject.Connection.Execute "INSERT INTO YourTAble(Col1, Col2, Col3, Col4, etc) VALUES(" & rst("FTIR_MEASNO") & "," & rst("FTIR_APNO") & "," & rst("FTIR_TITLE") & "," & rst("FTIR_RM") & ")"
  rst.MoveNext
Loop

Of course, if these are Text values, you'll need to enclose them in single quotes; if Dates, enclose them in hash marks.

Sorry didn't mean to shout just had the caps on by mistake.

k

How do I use the various fields that have various field types coming from Oracle, I ran into the problem early on and still haven't figured out how to solve that issue.

Sorry again for the mistaken cap lock.

K

ran into parameter Accessor issue.

How do I pass a parameter into Oracle from Access?

k

see '***********

ran into parameter Accessor issue.

 Public Function FTIRConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
 
    gAPNo = "12345"  'getgapno()
    'If IsTableExist("tblFTIR_vw") Then
        CurrentDb.Execute ("Delete * from tblFTIR_vw")
    sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
             "(Host=Test)(Port=1521)))(CONNECT_DATA=(SID=ftcsverf)));" & _
            "User Id=test;Password=test;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
strSQL = "SELECT FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
        " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
        " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
        " FTIR_LAST_REVISION_DATE" & _
        " FROM FTCSVERF.FTIR_DNLD_VI_VW" & _
        " WHERE FTIR_APNO='" & UCase(gAPNo) & "'"
Set adoRS = New ADODB.Recordset
adoRS.Open strSQL, adoConn, adOpenDynamic, adLockReadOnly
  Do Until adoRS.EOF
'*************Here  
CurrentProject.Connection.Execute _
      "INSERT INTO tblFTIR_vw(TIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE, DateCreated, User" & _
    " VALUES(" & adoRS("FTIR_MEASNO") & "," & adoRS("FTIR_APNO") & "," & _
        adoRS("FTIR_TITLE") & "," & adoRS("FTIR_RM") & "," & adoRS("FTIR_SPS") & "," & _
        adoRS("FTIR_MIN") & "," & adoRS("FTIR_MAX") & "," & adoRS("FTIR_UNITS") & "," & _
        adoRS("FTIR_ACC") & "," & adoRS("FTIR_TYPE") & "," & adoRS("FTIR_LOC") & "," & _
        adoRS("FTIR_FLTR") & "," & adoRS("FTIR_HCO") & "," & adoRS("FTIR_LCO") & "," & _
        adoRS("FTIR_BUSNAME") & "," & adoRS("FTIR_SU") & "," & adoRS("FTIR_EU_SU") & "," _
        & adoRS("FTIR_EU") & "," & adoRS("FTIR_SPHDL") & "," & adoRS("FTIR_DESC") & "," & _
        adoRS("FTIR_MAINTCD") & "," & adoRS("FTIR_CMT") & "," & adoRS("FTIR_INSTLDWGNO") _
        & "," & adoRS("FTIR_LAST_REVISOR") & "," & adoRS("FTIR_LAST_REVISION_DATE")
  adoRS.MoveNext
Loop
strSQL = "Select * from tblFTIR_vw"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do Until rs.EOF
    rs.Edit
    rs.Fields("DateCreated").Value = Date
    rs.Fields("User").Value = Environ("UserName")
    rs.Update
    rs.MoveNext
Loop

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:

Well, you'd need to determine what FieldTypes are coming in from Oracle and make sure those fields are going into valid FieldTypes in your Access tables. Here's a KB article that may help: http://support.microsoft.com/kb/104977. Once you build your table, and determine that the incoming Oracle fieldtypes are indeed going into a column that has been declared with the correct fieldType, you should be able to insert your data into the Access table.

Why would you need to pass a parameter to an Oracle table? Or are you running this against a Stored Procedure (or whatever they call them in Oracle)?

I need to limit the Oracle dataview to a particular item.

so this is not the issue of a paramter rather the field type issues?

k

How do I convert a Oracle number field to a Text field?

k

Here are the Oracle table fields & field types  The FTIR_Measno is normally a Text field w/i Access.  Do you suggest I convert  during the update of the table, or change the FTIR_MeasNO in my temp table to a number, then convert it to a CVar("FTIR_MEASNO") when I go to use it in the newly creaed temp table?


Column Name                  ID      Data Type                  Null?
FTIR_MEASNO                   1      NUMBER (7)      (ACcess has it as text field)            N
FTIR_APNO                   2      VARCHAR2 (8 Char)            N
FTIR_TITLE                   3      VARCHAR2 (24 Char)      Y
FTIR_RM                         4      VARCHAR2 (2 Char)            Y
FTIR_SPS                         5      NUMBER (8,2)            Y
FTIR_MIN                         6      VARCHAR2 (7 Char)            Y
FTIR_MAX                         7      VARCHAR2 (7 Char)            Y
FTIR_UNITS                   8      VARCHAR2 (6 Char)            Y
FTIR_ACC                          9      FLOAT                        Y
FTIR_TYPE                  10      VARCHAR2 (4 Char)            Y
FTIR_LOC                        11      VARCHAR2 (4 Char)            Y
FTIR_FLTR                  12      VARCHAR2 (3 Char)            Y
FTIR_HCO                        13      FLOAT                        Y
FTIR_LCO                        14      FLOAT                        Y
FTIR_BUSNAME                  15      VARCHAR2 (8 Char)            Y
FTIR_SU                        16      VARCHAR2 (6 Char)            Y
FTIR_EU_SU                  17      FLOAT                        Y
FTIR_EU                        18      FLOAT                        Y
FTIR_SPHDL                  19      VARCHAR2 (1 Char)            Y
FTIR_DESC                  20      VARCHAR2 (240 Char)      Y
FTIR_MAINTCD                  21      VARCHAR2 (1 Char)            N
FTIR_CMT                        22      VARCHAR2 (240 Char)      Y
FTIR_INSTLDWGNO            23      VARCHAR2 (14 Char)      Y
FTIR_LAST_REVISOR            24      VARCHAR2 (6 Char)            Y
FTIR_LAST_REVISION_DATE      25      DATE                        Y

I'd suggest you convert when you move the data to your local table, during the INSERT statement ... you can use the various CDate, CStr etc functions to do this.

Still having issues with the Accessors Error:  I know it is not the Parameter issue it is the syntax issue.  based on the table structure previously mentioned could you verify my syntax.   when I attempt to limit my query to figure out which field is causing the problem I am getting a Missing expression error and when I leave it intact I get the Accessors Error.

    Do Until adoRS.EOF
        CurrentProject.Connection.Execute _
            "INSERT INTO tblFTIR_vw(FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE) " & _
            " VALUES('" & adoRS("FTIR_MEASNO") & "','" & adoRS("FTIR_APNO") & _
                "'," & LTrim$(adoRS("FTIR_TITLE")) & "'," & adoRS("FTIR_RM") & ",'" & _
                adoRS("FTIR_SPS") & "','" & adoRS("FTIR_MIN") & "','" & _
                adoRS("FTIR_MAX") & "','" & adoRS("FTIR_UNITS") & ",'" & _
                adoRS("FTIR_ACC") & "','" & adoRS("FTIR_TYPE") & "','" & _
                adoRS("FTIR_LOC") & "','" & adoRS("FTIR_FLTR") & ",'" & _
                adoRS("FTIR_HCO") & ",'" & adoRS("FTIR_LCO") & "','" & _
                adoRS("FTIR_BUSNAME") & "','" & adoRS("FTIR_BUSNAME") & "','" & _
                adoRS("FTIR_SU") & ",'" & adoRS("FTIR_EU_SU") & ",'" & _
                adoRS("FTIR_EU") & "','" & adoRS("FTIR_SPHDL") & "','" & _
                adoRS("FTIR_DESC") & "','" & adoRS("FTIR_MAINTCD") & "','" & _
                adoRS("FTIR_CMT") & "','" & adoRS("FTIR_INSTLDWGNO") & "','" & _
                adoRS("FTIR_LAST_REVISOR") & "','" & _
                Format(adoRS("FTIR_LAST_REVISION_DATE"), "\#yyyy\-mm\-dd#") & ")"
 
stripped out version - Gets missing expression error>
 
          CurrentProject.Connection.Execute _
            "INSERT INTO tblFTIR_vw(FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS)" & _
             " VALUES('" & adoRS("FTIR_MEASNO") & "','" & adoRS("FTIR_APNO") & "'," & _
                LTrim$(adoRS("FTIR_TITLE")) & "'," & adoRS("FTIR_RM") & ",'" & _
                adoRS("FTIR_SPS") & "','" & adoRS("FTIR_MIN") & "','" & _
                adoRS("FTIR_MAX") & "','" & adoRS("FTIR_UNITS") & ")"
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

You don't have an ending single quote on the last item:

 adoRS("FTIR_LAST_REVISOR") & "','" & _
                Format(adoRS("FTIR_LAST_REVISION_DATE"), "\#yyyy\-mm\-dd#") & "')"
-----------------------------------------------------------------------------------------------------^<<<  Added here

Also, is FTIR_Last_Revision_Date a Date field or a Text field? If it's a Date field, try surrounding it with hash marks ( # ).

Note that you can normally check your syntax pretty easily by "running" it in the Immediate window, then copy/paste the SQL into a new, blank query ... then switch to design view and Access will often tell you what (and where) the problem lies. To run the sql, just copy eventhing from the "INSERT INTO (including the double quote) and paste it into the Immediate window, add a ? before the whole thing then press enter ... the immediate window will show you the resultant string which you can then paste into a blank query for testing.

Yes, I tried your suggestions, and I am still getting type mismatch issues, when it comes to the Null values.

I cam across the follwoing function - is there a way to convert this to use the DSN-less type of connection string.

Also coulld I use a QueryDef type of code to actual view the data without using an updating of table - which causes the issue with datatype conversions?

Function fPassThrough(strSQL As String, strDSN)
   
    Dim db As Database
    Dim qdf As QueryDef
   
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", strSQL)
   
    With qdf
        .ReturnsRecords = False
        .Connect = "ODBC;DSN=" & strDSN
        .Execute
        .Close
    End With
   
End Function

You can try setting the Connect setting to a valid connect string ... you might also be able to simply view the data as well by just opening your query.

For info on connect strings try www.connectionstrings.com

<Yes, I tried your suggestions, and I am still getting type mismatch issues, when it comes to the Null values.>

Anytime you are working with potential Null values you'll need to handle them ... in Access, the easiest way is to use the Nz function on EACH column:

 "INSERT INTO tblFTIR_vw(FTIR_MEASNO, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR, FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU," & _
                " FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT, FTIR_INSTLDWGNO, FTIR_LAST_REVISOR," & _
                " FTIR_LAST_REVISION_DATE) " & _
            " VALUES('" & Nz(adoRS("FTIR_MEASNO"),'') & "','" & Nz(adoRS("FTIR_APNO"),'') & _
                "'," & LTrim$(Nz(adoRS("FTIR_TITLE"),'')) & "'," & Nz(adoRS("FTIR_RM"),'') & ",'"  etc etc

When I tried the Nz(), still got the same error, I even tried Nz(fieldName),0) around those fields that = numbers.  Still no luck.

Trying different approach - changing the insert into an existing table - I am attempting to Create TAble on the fly with the Oracle Table using the fPassThrough Code.  Having some connection issues, maybe you could assist me and is this a feasible way to approach the datatype conversion issues?  Which would you recommend?

k

<Edited by AnnieMod to remove sensitive information>

Connection for Create TAble:
 
Public Function ConnectFTIR()
On Error GoTo ConnectFTIR_Error
 
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim RS As ADODB.Recordset
 
    gAPNo = "test" 'GetgAPNo()
    If IsTableExist("table_vw") Then
        CurrentDb.Execute ("Drop table table_vw")
    End If
 
Set conn = New ADODB.Connection
 
adoConn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
             "(Host=something.com)(Port=1521)))(CONNECT_DATA=(SID=something)));" & _
            "User Id=someone;Password=password;"
    Set adoConn = New ADODB.Connection
    adoConn.Open
 
adoConn.Execute <create table statement removed>
    Set conn = New ADODB.Connection
    conn.Open sConn
    Set adoRS = New ADODB.Recordset
   ' adoRS.Open strSQL, conn, adOpenDynamic, adLockReadOnly
 
conn.Close
 
ConnectFTIR_Exit:
Exit Function
 
ConnectFTIR_Error:
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "ConnectFTIR"
Resume ConnectFTIR_Exit
 
Pass Thru code:
Function fPassThrough(strSQL As String, strDSN)
   
    Dim db As Database
    Dim qdf As QueryDef
   
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", strSQL)
   
    With qdf
        .ReturnsRecords = False
        .Connect = "ODBC;DSN=" & strDSN
        .Execute
        .Close
    End With
   
End Function
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:

What is the proper syntax on a text field value that contains a single quote contained in the text.

ie.  'CONTROL COL POS PILOT'S'  so it is not read as the end of the text?

k

Since I am using Oracle data the Chr(34) is not recognized.