Oracle Passthrough to Access - works in Access, not in VB App

[ClimoC]

Hi

Reposting, as I think I might've put it in the wrong forum.

The following works beautifully, when run from a module in an open Access Database

Sub TestDump()

Dim db As Database
   Dim LPassThrough As QueryDef, tblDef As TableDef
   Dim DStmp As String, DayOnlyStmp As String
    
    DStmp = CStr(Format(Now(), "yyyymmdd_hhmmss"))
    DayOnlyStmp = CStr(Format(Now(), "yyyymmdd"))

DStmp = Cstr(Format(Now(), "yyyymmdd"))

Set db = DBEngine.CreateDatabase("C:\Users\climoc\Desktop\" & DStmp, dbLangGeneral)
Set tblDef = db.CreateTableDef(DStmp)

With tblDef
.Fields.Append .CreateField("FIELD1", dbText)
.Fields.Append .CreateField("FIELD2", dbText)
End with

db.TableDefs.Appen(tblDef)

Set LPT = db.CreateQueryDef("qryTmp7")

connstr = "ODBC;Driver={Microsoft ODBC for Oracle};Server=MYSRVR;uid=USER;pwd=A!B2C£"

With LPT
.Connect = newstr
.SQL = "SELECT FIELD1, FIELD2 FROM OraTbl WHERE FIELD2 = 'ATextValue'"
.ReturnsRecords = True
.Close
End With

db.Execute "INSERT INTO " & DStmp & "([FIELD1], [FIELD2]) SELECT qryTmp7.[FIELD1], qryTmp7.[FIELD2] FROM qryTmp7"

db.QueryDefs.Delete "qryTmp7"

End Sub

But in Visual Studio, this executes without error - but all that happens is that the Database, Table, and Query are created - but the .Execute line doesn't insert any records to the table.

Dim AccApp As New Microsoft.Office.Interop.Access.Application
        Dim db As Microsoft.Office.Interop.Access.Dao.Database
        Dim LPassThrough As Microsoft.Office.Interop.Access.Dao.QueryDef, tblDef As Microsoft.Office.Interop.Access.Dao.TableDef
        Dim DStmp As String, DayOnlyStmp As String
        Dim newstr As String

DStmp = Cstr(Format(Now(),"yyyymmdd"))

db = AccApp.DBEngine.CreateDatabase("C:\Users\climoc\Desktop\" & DStmp, Dao.LanuageConstants.dbLangGeneral)

tblDef = db.CreateTableDef(DStmp)

With tblDef
.Fields.Append(.CreateField("FIELD1",10))
.Fields.Append(.CreateField("FIELD2",10))
End with

db.TableDefs.Append(tblDef)
LPT = db.CreateQueryDef("qryTmp7")

connstr = "ODBC;Driver={Microsoft ODBC for Oracle};Server=MYSRVR;uid=USER;pwd=A!B2C£"

With LPT
.Connect() = newstr
.SQL = "SELECT FIELD1, FIELD2 FROM OraTbl WHERE FIELD2 = 'ATextValue'"
.ReturnsRecords() = True
.Close()
End With

db.Execute("INSERT INTO " & DStmp & "([FIELD1], [FIELD2]) SELECT qryTmp7.[FIELD1], qryTmp7.[FIELD2] FROM qryTmp7")

Any thoughts? Help please? This is really getting to me and getting in the way of my little development!

Thanks

 

[ClimoC]

Hi

Reposting, as I think I might've put it in the wrong forum.

The following works beautifully, when run from a module in an open Access Database

Sub TestDump()

Dim db As Database
   Dim LPassThrough As QueryDef, tblDef As TableDef
   Dim DStmp As String, DayOnlyStmp As String
    
    DStmp = CStr(Format(Now(), "yyyymmdd_hhmmss"))
    DayOnlyStmp = CStr(Format(Now(), "yyyymmdd"))

DStmp = Cstr(Format(Now(), "yyyymmdd"))

Set db = DBEngine.CreateDatabase("C:\Users\climoc\Desktop\" & DStmp, dbLangGeneral)
Set tblDef = db.CreateTableDef(DStmp)

With tblDef
.Fields.Append .CreateField("FIELD1", dbText)
.Fields.Append .CreateField("FIELD2", dbText)
End with

db.TableDefs.Appen(tblDef)

Set LPT = db.CreateQueryDef("qryTmp7")

connstr = "ODBC;Driver={Microsoft ODBC for Oracle};Server=MYSRVR;uid=USER;pwd=A!B2C£"

With LPT
.Connect = newstr
.SQL = "SELECT FIELD1, FIELD2 FROM OraTbl WHERE FIELD2 = 'ATextValue'"
.ReturnsRecords = True
.Close
End With

db.Execute "INSERT INTO " & DStmp & "([FIELD1], [FIELD2]) SELECT qryTmp7.[FIELD1], qryTmp7.[FIELD2] FROM qryTmp7"

db.QueryDefs.Delete "qryTmp7"

End Sub

But in Visual Studio, this executes without error - but all that happens is that the Database, Table, and Query are created - but the .Execute line doesn't insert any records to the table.

Dim AccApp As New Microsoft.Office.Interop.Access.Application
        Dim db As Microsoft.Office.Interop.Access.Dao.Database
        Dim LPassThrough As Microsoft.Office.Interop.Access.Dao.QueryDef, tblDef As Microsoft.Office.Interop.Access.Dao.TableDef
        Dim DStmp As String, DayOnlyStmp As String
        Dim newstr As String

DStmp = Cstr(Format(Now(),"yyyymmdd"))

db = AccApp.DBEngine.CreateDatabase("C:\Users\climoc\Desktop\" & DStmp, Dao.LanuageConstants.dbLangGeneral)

tblDef = db.CreateTableDef(DStmp)

With tblDef
.Fields.Append(.CreateField("FIELD1",10))
.Fields.Append(.CreateField("FIELD2",10))
End with

db.TableDefs.Append(tblDef)
LPT = db.CreateQueryDef("qryTmp7")

connstr = "ODBC;Driver={Microsoft ODBC for Oracle};Server=MYSRVR;uid=USER;pwd=A!B2C£"

With LPT
.Connect() = newstr
.SQL = "SELECT FIELD1, FIELD2 FROM OraTbl WHERE FIELD2 = 'ATextValue'"
.ReturnsRecords() = True
.Close()
End With

db.Execute("INSERT INTO " & DStmp & "([FIELD1], [FIELD2]) SELECT qryTmp7.[FIELD1], qryTmp7.[FIELD2] FROM qryTmp7")

Any thoughts? Help please? This is really getting to me and getting in the way of my little development!

Thanks

Embarrassingly - And I'm yet to figure out why - The line 'Format(NOW(),"yyyymmdd")' returns a funny string in VB.

It evaluates it to 20143320 - for the 20th of March.

That's why it was returning 0 records - there is no month no. 33

 

[AngelJ]

Sorry, I didn't look through your code in detail, but note the difference between the Visual Basic Format function: Format Function

mm	Displays the minute as a number with leading zeros (for example, 12:01:15).

<tbody>
</tbody>

and the VBA Format function: Format Function (Visual Basic for Applications)

mm	Display the month as a number with a leading zero (01 – 12). If m immediately follows h or hh, the minute rather than the month is displayed.

<tbody>
</tbody>

 

[james_lankford]

assuming this is a typo

connstr = "ODBC;Driver={Microsoft ODBC for Oracle};Server=MYSRVR;uid=USER;pwd=A!B2C£"

With LPT 
  .Connect = newstr