TableDefination of DAO 3.6 is not working in Windows XP (MDAC -2.8

[Nilesh Malde]

Following code is working in windows 2000 which uses MDAC 2.5 while same is
not working in Windows XP which uses MDAC 2.8. This is regarding Table
defination of DAO 3.6. Anybody has solution to work this code on Windows xp.
=====================================================
Public Sub ProcessDatabase(DSNSource As String, ServerName As String,
ProjectName As String, DBUser As String, DBPwd As Variant, TransferType As
String)

Dim loginResult As Integer
Dim tbl As Variant
On Error GoTo LoginError
Dim msg As String
Dim MTOSchema As String
Dim PDSchema As String
Dim dbconnect As String
Dim tdf As DAO.TableDef, dbs As DAO.Database
Dim connect As DAO.Connection


MTOSchema = "mto_" & ProjectName
PDSchema = "pd_" & ProjectName
dbconnect = "ODBC;DSN=" & DSNSource & ";UID=" & DBUser & ";SERVER=" &
ServerName & ";PWD=" & DBPwd

MTOtableList = Array("pdtable_12", "pdtable_34", "pdtable_67",
"pdtable_50", "pdtable_80", _
"pdtable_250")
PDtableList = Array("pdtable_101", "pdtable_111", "pdtable_112",
"pdtable_113", "pdtable_121", _
"pdtable_122", "pdtable_123", "pdtable_124",
"pdtable_125", "pdtable_126", "pdtable_127", _
"pdtable_128", "pdtable_131", "pdtable_132",
"pdtable_133", "pdtable_134", _
"pdtable_135", "pdtable_136", "pdtable_181",
"pdtable_182", "pdtable_183", _
"pdtable_184", "pdtable_185", "pdtable_186",
"pdtable_187", "pdtable_188", _
"pdtable_189")


'Delete Tables if they already exist
Call CheckDuplicateTables

' Set connect.connect = dbconnect
Set dbs = CurrentDb

For Each tbl In MTOtableList
If (TransferType = "import") Then
DoCmd.TransferDatabase acImport, "ODBC Database", dbconnect, _
acTable, MTOSchema & "." & tbl, tbl
Else
Set tdf = dbs.CreateTableDef(tbl)
tdf.connect = dbconnect
tdf.SourceTableName = MTOSchema & "." & tbl
dbs.TableDefs.Append tdf
End If
Next tbl

For Each tbl In PDtableList
If (TransferType = "import") Then
DoCmd.TransferDatabase acImport, "ODBC Database", dbconnect, _
acTable, PDSchema & "." & tbl, tbl
Else
Set tdf = dbs.CreateTableDef(tbl)
tdf.connect = dbconnect
tdf.SourceTableName = PDSchema & "." & tbl
dbs.TableDefs.Append tdf
End If
Next tbl

DoCmd.Close acForm, "frmImportData", acSaveNo
msg = "Data Imported/Linked Successfully"
intresult = MsgBox(msg, vbOKOnly + vbExclamation, "Import/Link Data")
DoCmd.OpenForm "FrmGenReport"

Exit Sub

LoginError:
DoCmd.Close acForm, "frmImportMsg", acSaveNo

If Err.Number <> 3011 Then
' msg = "Could Not Connect to Datasource!@Data Source: " & DSNSource
& ", Server: " & ServerName & _
' "@ProjectName: " & MTOSchema & ", Passwd: " & MTOSchema & ",
ErrNo: " & Str(Err.Number)
' intResult = MsgBox(msg, vbOKOnly + vbExclamation, "Import Error")
MsgBox Err.Number & " - " & Err.Description
Else
msg = "Could not import Table!" & "@The ODBC Source does not
contain: " & tbl & _
"@Click on Ok to continue with import"
intresult = MsgBox(msg, vbOKOnly + vbExclamation, "Invalid Table No")
Resume Next
End If

 

[Stefan Hoffmann]

hi Nilesh,

Following code is working in windows 2000 which uses MDAC 2.5 while same is
not working in Windows XP which uses MDAC 2.8. This is regarding Table
defination of DAO 3.6. Anybody has solution to work this code on Windows xp.

What error message do you get?


mfG
--> stefan <--

 

[Nilesh Malde]

Dear stefan,

Thanks for reply. Actually I diagnose that it's not creating table defination.
Error is "Invalid Table No", Could not import table !@ The ODBC Source does
not containdtable_101@click on Ok to continue with import

Like this error will come for all tables which are listed in array.

from this command program will go to error

" dbs.TableDefs.Append tdf "

 

[Stefan Hoffmann]

hi Nilesh,

Thanks for reply. Actually I diagnose that it's not creating table defination.

Try

dbs.TableDefs.Append _
dbs.CreateTableDef(LocalDestinationName, 0, _
SourceNameInclSchema, CONNECTION_ODBC)

Error is "Invalid Table No", Could not import table !@ The ODBC Source does
not containdtable_101@click on Ok to continue with import

Have you checked the permissions on the Oracle tables?


mfG
--> stefan <--

 

[Nilesh Malde]

It's not working and giving same error no. 3011.

 

[Stefan Hoffmann]

hi Nilesh,

It's not working and giving same error no. 3011.

The "@click" in your table name "pdtable_101@click" is a database link.

Have you checked your permissions for it? Is your database link working
in Oracle?


mfG
--> stefan <--

 

[Aaron Kempf]

why in the heck would you use DAO?

seriously

stop smoking crack, DAO hasn't been included with Windows, Office or MDAC
for a decade.

do the math, kid

 

[Robert Morley]

Same question as previous thread: why are you resurrecting a month old
thread with your vitriol?

You used to be black-listed on some MS servers...apparently someone needs to
put you back on it.



Rob

 

[Stefan Hoffmann]

hi Robert,

Same question as previous thread: why are you resurrecting a month old
thread with your vitriol?

Ignore him or praise god for his wisdom to create brainless zombies


mfG
--> stefan <--

 

[Robert Morley]

I'm not sure I'd call it "wisdom" (ignoring for the moment, that I'm Pagan
and don't habitually praise god for anything <grin>).


Rob