Cannot OpenRecordset on back-end database (1 Viewer)

GreenWinter · Jun 28, 2016

I split database into front and back ends. When I try to open the record in back-end, I get run-time error 3011 saying database engine could find the table "tbl_GL_0050_Daily" with the line

Code:

Set rs = dbs.OpenRecordset("tbl_GL_0050_Daily")

highlighted. If I comment the

Code:

DoCmd.OpenQuery "qry_GL_0050_Daily_Report", acViewNormal, acEdit

, there is no more errors. That function of the query is to select data into table "tbl_GL_0050_Daily" that means deleting the old one and remake a new table with the same name.

Could anyone help with the issue? Thanks in advance.

Code:

Dim dbs      As DAO.Database
Dim conStr   As String
Dim password As String
Dim rs       As DAO.Recordset
Dim temp     As DAO.TableDef
Dim tdf      As DAO.TableDef

password = ""
conStr = "MS Access;PWD=" & password
Set dbs = OpenDatabase("C:\db.accdb", False, False, conStr)

DoCmd.OpenQuery "qry_GL_0050_Daily_Report", acViewNormal, acEdit
Set rs = dbs.OpenRecordset("tbl_GL_0050_Daily")
rs.Close

CJ_London · Jun 28, 2016

not quite sure why you are doing what you are doing

If you have a FE/BE setup the FE would contain a linked table to the backend - which presumably the query is utilising

in which case your code would be

Set rs = currentdb.OpenRecordset("tbl_GL_0050_Daily")

GreenWinter · Jun 29, 2016

Yes. The query I used is to add fields to the table which I think cannot be implemented from FE. So I wanted to alter the BE table directly. I'm a beginner in ACCESS. Please point out if anything wrong with me. Thank you!

CJ_London said:
not quite sure why you are doing what you are doing

If you have a FE/BE setup the FE would contain a linked table to the backend - which presumably the query is utilising

in which case your code would be

Set rs = currentdb.OpenRecordset("tbl_GL_0050_Daily")

CJ_London · Jun 29, 2016

yes - you shouldn't be adding fields to the table - that is Excel thinking. From your brief description you are building 'data' into your design which is wrong.

instead of fields like this

Account...201601...201602...201603....
1234........1000.......1500......1250...
3456........750.........600........1400....

have

Account...YrMth....Amount
1234.......201601..1000
1234.......201602..1500
1234.......201603..1250
3456.......201601..750
3456.......201602..600
3456.......201603..1400

GreenWinter · Jun 29, 2016

Thanks for your advice. I will change the table structure as what you said. But I'm still unsure about the issue in my post. Could you please explain the mechanism behind?

CJ_London said:
yes - you shouldn't be adding fields to the table - that is Excel thinking. From your brief description you are building 'data' into your design which is wrong.

instead of fields like this

Account...201601...201602...201603....
1234........1000.......1500......1250...
3456........750.........600........1400....

have

Account...YrMth....Amount
1234.......201601..1000
1234.......201602..1500
1234.......201603..1250
3456.......201601..750
3456.......201602..600
3456.......201603..1400

CJ_London · Jun 29, 2016

Regret not - I don' understand what you mean by

If I comment the
Code:
DoCmd.OpenQuery "qry_GL_0050_Daily_Report", acViewNormal, acEdit
, there is no more errors. That function of the query is to select data into table "tbl_GL_0050_Daily" that means deleting the old one and remake a new table with the same name.

docmd.openquery will open in the current database (not the back end, although may be referring to a linked table). And it can only do one function - might be deleting the old one, might be making a new one - don't know without seeing the SQL and won't work on the backend. To make chages to the backend tables (delete, make, whatever) you would need to use

dbs.execute

It is generally not a good idea to delete and remake tables:

a) they will bloat the db unnecessarily
b) the resultant table will not be indexed so will be slow
c) you cannot maintain relationships

Cannot OpenRecordset on back-end database (1 Viewer)

GreenWinter

New member

CJ_London

Super Moderator

GreenWinter

New member

CJ_London

Super Moderator

GreenWinter

New member

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread