I have a problem which I can not get my head around. I have an Access database where I am trying to create a set of queries in by using VBA in Excel by use of ADODB.
I have several queries that are successfully created, ie. the one below here. The second will however not work. If I copy the query manually to the access database (From SELECT and forward) and save is as "DK_Teledata_1" it works perfect
CREATE PROCEDURE DK_Aktiviteter_Union_1 AS SELECT DK_Aktivitet.År FROM DK_Aktivitet;
And the one that does not work
CREATE PROCEDURE DK_Teledata_1 AS SELECT DK_Teledata.Dato FROM DK_Teledata INNER JOIN Time_Intervals ON DK_Teledata.Interval = Time_Intervals.Time_Interval;
The following queries with the same data are created without any problems:
CREATE PROCEDURE DK_Teledata_1 AS SELECT * FROM DK_Teledata;
CREATE PROCEDURE DK_Teledata_1 AS SELECT * FROM Time_Intervals;
CREATE PROCEDURE DK_Teledata_1 AS SELECT * FROM Time_Intervals, DK_Teledata;
As soon as I create the join or use a "WHERE DK_Teledata.Interval = Time_Intervals.Time_Interval" it fails.
The fields Intervals and Time_Intervals are both Text fields with the same length and properties.
Using Excel 2010, accdb database file and "Microsoft.ACE.OLEDB.12.0" provider string.
As a wild guess, I'll suggest you substitute CREATE VIEW for CREATE PROCEDURE.
If that also doesn't work, you can use DAO to create your query.
Dim strSql As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
strSql = "SELECT DK_Teledata.Dato" & vbCrLf & _
"FROM DK_Teledata INNER JOIN Time_Intervals" & vbCrLf & _
"ON DK_Teledata.Interval = Time_Intervals.Time_Interval;"
Set db = OpenDatabase("C:\db_folder\your.accdb", True, False)
Set qdf = db.CreateQueryDef("DK_Teledata_1", strSql)
That code uses early binding so requires setting a reference. But you could do the same thing with late binding which doesn't require a reference.
Dim strSql As String
Dim dbe As Object
Dim db As Object
Dim qdf As Object
strSql = "SELECT DK_Teledata.Dato" & vbCrLf & _
"FROM DK_Teledata INNER JOIN Time_Intervals" & vbCrLf & _
"ON DK_Teledata.Interval = Time_Intervals.Time_Interval;"
Set dbe = CreateObject("DAO.DBEngine.120")
Set db = dbe.OpenDatabase("C:\db_folder\your.accdb", True, False)
Set qdf = db.CreateQueryDef("DK_Teledata_1", strSql)