TonyWootton
asked on
Why do I get "System Resource exceeded" when I linked second & successive Excel tables
Public Sub Per_VatCodeData(ByVal Period As Long, ByVal CoyCode As String, ByVal VatCode As String, ByVal VatBook As String)
Dim tdfLink As TableDef
I need to use SQL to append data to a series of Excel sheets which I was trying to link to an Access MDB using the following fragment of code in a repeatedly executed Sub:
**************************
Set tdfLink = CurrentDb.CreateTableDef("
tdfLink.Connect = "Excel 8.0;DATABASE=" & VatBook
tdfLink.SourceTableName = VatCode & "dyn"
CurrentDb.TableDefs.Append
str = Xql_RecordSet(VatCode)
DoCmd.RunSQL str
Set tdfLink = Nothing
CurrentDb.TableDefs.Delete
*************************
Unfortunately, although it works once, it subsequently stops with the error 3035 and message "System Resource exceeded".
Am I doing something wrong? Does anyone have a solution? This should be the fastest way of doing what I wish, if only .....
Thanks
Dim tdfLink As TableDef
I need to use SQL to append data to a series of Excel sheets which I was trying to link to an Access MDB using the following fragment of code in a repeatedly executed Sub:
**************************
Set tdfLink = CurrentDb.CreateTableDef("
tdfLink.Connect = "Excel 8.0;DATABASE=" & VatBook
tdfLink.SourceTableName = VatCode & "dyn"
CurrentDb.TableDefs.Append
str = Xql_RecordSet(VatCode)
DoCmd.RunSQL str
Set tdfLink = Nothing
CurrentDb.TableDefs.Delete
*************************
Unfortunately, although it works once, it subsequently stops with the error 3035 and message "System Resource exceeded".
Am I doing something wrong? Does anyone have a solution? This should be the fastest way of doing what I wish, if only .....
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Although I am grateful for your response and I've looked at both the questions you refer to, whilst the error message I get is the same, I don't think that the cause is. Since I asked it, I've tried linking all of the sheets before populating them. That worked for all 22. I am then able to write to the first attached sheet, but then to no others. I am subsequently unable to even open any of the attached sheets, least of all to write to them. My suspicion is that there is some kind of problem with the DAO/ODBC mechanism, though I have no idea what and I have been unable to find away to correct it.
I therefore tried another route, by using automation to make Access control Excel which then sucks data out of Access. A somewhat roundabout route, except that it works quite quickly until Excel doesn't quite terminate properly (see Q_21029622.html). Fortunately I've found a workaround