I am getting a Compiler Error "Method or Data Member not found" when trying to create a field with my macro. I am using Excel as my interface but using Access (OLE). I have referenced the appropriate DAO and Access Libraries and have assigned my definitions to DAO, a common fix for the problem I am having. I know that it is using access properly because when trying to merely populate an already created table the macro works. It fails to work when I try to create a table, here are the necessary parts of my code (at least I believe them to be the necessary parts):
Dim db As DAO.Database
Dim rs1 As Recordset, rs2 As Recordset
Dim fldnmbr As Long
Dim Accessapp As Object
Dim Atlas As String
Dim tbl As DAO.TableDefs
Dim fld As DAO.Field
Atlas = ActiveWorkbook.Path & "\Consultation Atlas.mdb"
Set Accessapp = GetObject(Atlas, "Access.Database")
Set db = Accessapp.CurrentDb
Set tbl = db.CreateTableDef("Germany Results")
Set rs1 = db.OpenRecordset("Germany")
rs1.MoveFirst
tbl.Fields.Append tbl.CreateField(rs1.Fields(0).Name, dbText)
tbl.Fields.Append tbl.CreateField(rs1.Fields(1).Name, dbText)
tbl.Fields.Append tbl.CreateField(rs1.Fields(2).Name, dbText)
db.TableDefs.Append tbl
db.TableDefs.Refresh
Accessapp.Quit
Set Accessapp = Nothing
anybody have any ideas where I'm going wrong with this? I'm not a developer just a technical user. i used the code as described in the access vba editor help section, and have tried other iterations found online but none work. if somebody could help me out I would owe you my life! Thanks!
Dim db As DAO.Database
Dim rs1 As Recordset, rs2 As Recordset
Dim fldnmbr As Long
Dim Accessapp As Object
Dim Atlas As String
Dim tbl As DAO.TableDefs
Dim fld As DAO.Field
Atlas = ActiveWorkbook.Path & "\Consultation Atlas.mdb"
Set Accessapp = GetObject(Atlas, "Access.Database")
Set db = Accessapp.CurrentDb
Set tbl = db.CreateTableDef("Germany Results")
Set rs1 = db.OpenRecordset("Germany")
rs1.MoveFirst
tbl.Fields.Append tbl.CreateField(rs1.Fields(0).Name, dbText)
tbl.Fields.Append tbl.CreateField(rs1.Fields(1).Name, dbText)
tbl.Fields.Append tbl.CreateField(rs1.Fields(2).Name, dbText)
db.TableDefs.Append tbl
db.TableDefs.Refresh
Accessapp.Quit
Set Accessapp = Nothing
anybody have any ideas where I'm going wrong with this? I'm not a developer just a technical user. i used the code as described in the access vba editor help section, and have tried other iterations found online but none work. if somebody could help me out I would owe you my life! Thanks!