Hello all,
First of all, I just want to say that I am upgrading to a newer version of Access for a company as they were on Access 97 (first time upgrader). They are still currently using this old version until I complete the upgrade. I upgraded from 97 to 2003, and eventually I need to upgrade form 2003 to 2010. Everything seemed fine until I went to connect to the database server. I was not the original writer of this code, and the person that wrote it is long gone.
Basically how this works is, the first time the person opens up a form each day, we talk with the database server. We loop through all the tables in the local copy to determine if there is a table on the server. The table is then deleted, and recreated using the data from the server. We do this because there is licensing issues with the data server, so it limits how many connections at a time. So it appears their choice was to, once a day, the data would be pulled to a local copy that can be accessed so it is faster and they don't have to pay for too many licenses.
The process worked perfectly fine in 97, but as soon as 2003 upgrade it fails at this line (I wanted to test before upgrading to 2010 and have more issues):
The line that errors out is the DoCmd.TransferDatabase line.
The table gets deleted from the local copy, but is not recreated within access, and then when it hits the second table it just throws a 3011 run-time error. If more code is needed let me know.
Any help would be appreciated, and I look forward to hearing some ideas as I am utterly stumped why this is failing. With me being a beginner maybe it is something easy that I am simply over looking.
Thanks,
EFox
First of all, I just want to say that I am upgrading to a newer version of Access for a company as they were on Access 97 (first time upgrader). They are still currently using this old version until I complete the upgrade. I upgraded from 97 to 2003, and eventually I need to upgrade form 2003 to 2010. Everything seemed fine until I went to connect to the database server. I was not the original writer of this code, and the person that wrote it is long gone.
Basically how this works is, the first time the person opens up a form each day, we talk with the database server. We loop through all the tables in the local copy to determine if there is a table on the server. The table is then deleted, and recreated using the data from the server. We do this because there is licensing issues with the data server, so it limits how many connections at a time. So it appears their choice was to, once a day, the data would be pulled to a local copy that can be accessed so it is faster and they don't have to pay for too many licenses.
The process worked perfectly fine in 97, but as soon as 2003 upgrade it fails at this line (I wanted to test before upgrading to 2010 and have more issues):
Code:
Set rs = CurrentDb.OpenRecordset("OdysseyTables", dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
For x = 1 To rs.RecordCount
Forms![DailyEvent]![Msg] = Forms![DailyEvent]![Msg] & rt & "Getting table: " & rs!OdysseyTbl
tempTblName = "PUB_" & Right$(rs!OdysseyTbl, Len(rs!OdysseyTbl) - 4)
If fTableExists(tempTblName) = True Then DoCmd.DeleteObject acTable, tempTblName
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DATABASE=maindb;UID=uid;PWD=pass;DSN=dsnname", acTable, rs!OdysseyTbl, rs!OdysseyTbl, False, FalseThe table gets deleted from the local copy, but is not recreated within access, and then when it hits the second table it just throws a 3011 run-time error. If more code is needed let me know.
Any help would be appreciated, and I look forward to hearing some ideas as I am utterly stumped why this is failing. With me being a beginner maybe it is something easy that I am simply over looking.
Thanks,
EFox
