Working Passthrough in Access, doesn't work in VB6?

[ClimoC]

Hey hey

Pretty sure I've got all the right references etc, but the following works in Access VBA:

Sub TestDump()

Dim db as Database
Dim LPT as QueryDef, tblDef as TableDef
Dim DStmp as string, connstr as String

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

It works brilliantly.

But then when I move it into my VB6 module in Visual Studio, the following doesn't work (it doesn't throw an error or bug). It creates the database file and the table (and if I remove the 'QueryDefs.Delete "qryTmp7"' line at the end, the Query stays and looks fine too) - but no records are added to the table from the returned passThrough query.

Dim AccApp as new Microsoft.Office.Interop.Access.Application
Dim db as DAO.Database
Dim LTP as DAO.QueryDef, tblDef as DAO.TableDef
Dim DStmp as String, connstr 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")

Anyone spot or know of anything tricky about doing DAO/Access style things from VB6?

Thanks
C

 

[ClimoC]

Hey hey

Pretty sure I've got all the right references etc, but the following works in Access VBA:

Sub TestDump()

Dim db as Database
Dim LPT as QueryDef, tblDef as TableDef
Dim DStmp as string, connstr as String

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

It works brilliantly.

But then when I move it into my VB6 module in Visual Studio, the following doesn't work (it doesn't throw an error or bug). It creates the database file and the table (and if I remove the 'QueryDefs.Delete "qryTmp7"' line at the end, the Query stays and looks fine too) - but no records are added to the table from the returned passThrough query.

Dim AccApp as new Microsoft.Office.Interop.Access.Application
Dim db as DAO.Database
Dim LTP as DAO.QueryDef, tblDef as DAO.TableDef
Dim DStmp as String, connstr 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")

Anyone spot or know of anything tricky about doing DAO/Access style things from VB6?

Thanks
C

NB: I have since looked up the library that 'QueryDef' and 'TableDef' come from, which is Access itself, not the DAO library - but the same thing happens. Table/Database created, no records appended.

Help?!