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

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
Hey hey

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

Code:
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.

Code:
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

Well-known Member
Joined
Aug 21, 2009
Messages
584
Hey hey

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

Code:
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.

Code:
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?!
 

Forum statistics

Threads
1,085,046
Messages
5,381,360
Members
401,735
Latest member
ranjithr

Some videos you may like

This Week's Hot Topics

Top