Cannot OpenRecordset on back-end database

[GreenWinter]

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

Set rs = dbs.OpenRecordset("tbl_GL_0050_Daily")

highlighted. If I comment the

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.

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]

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]

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!

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]

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]

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?

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]

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