CODEWITHSUNDEEP
sql-server-2005ms-accessvbaado
lamwaiman1988
lamwaiman1988

Reputation: 3742

MS access and ODBC linked table and VBA

I got a ms access mdb file which contains some tables. 1 is a local table in the mdb, and 2 more tables which linked with odbc on a remote ms sql server 2005. I made a form and a vba code to execute a query on the tables. But I am getting error. The 2 tables are actually views.

Here is the part of my code:

   thisMonthTable = "dbo_inbound_rated_all_" & currentYear & currentMonth
   If (currentMonth = "12") Then
        nextMonthTable = "dbo_inbound_rated_all_" & nextYear & nextMonth
   Else
        nextMonthTable = "dbo_inbound_rated_all_" & currentYear & nextMonth
   End If

   With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT A.* FROM " & nextMonthTable & " A Inner Join opt_in_customer_record B on A.imsi_number = B.imsi where Datevalue(A.call_date) >= Datevalue(B.start_date) and Datevalue(A.call_date) <= (Datevalue(B.start_date) + val(LEFT(B.event_plan_code, 1))) "
    .CommandType = adCmdText
   End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
   End With

For your information, the value of thisMonthTable / nextMonthTable and the query are:

SQL:

SELECT     A.* 
FROM       ( dbo_inbound_rated_all_201012 
OR         dbo_inbound_rated_all_201101 ) A 
INNER JOIN opt_in_customer_record B 
ON         A.imsi_number = B.imsi 
WHERE      Datevalue(A.call_date) >= Datevalue(B.start_date) 
AND        Datevalue(A.call_date) <= (Datevalue(B.start_date) + val(LEFT(B.event_plan_code, 1)))

When I query in vba with thisMonthTable (dbo_inbound_rated_all_201012) , the query is fine. But when I query in vba with nextMonthTable (dbo_inbound_rated_all_201101), I got an error:

Run-time error "-2147217900 (80040e14)" Syntax error in from clause.

But when I run these query in ms access, both of them are running fine.

The queries are exactly the same, except the table name is different. I am sure the 2 views exist in both my mdb file as linked table and in the remote sql server.

I've lookup the internet about error 80040e14, some say it is related to a keyword being used as variable/column name, but this is not the case here, really.

P.S. I must state again that the queries run perfectly under ms-access's query , in contrast to the queries made in the vba code via ado, so the 2 views should be okay and correct.

Upvotes: 1

Views: 1109

Answers (3)

Judah Sali
Judah Sali

Reputation: 1098

This is mostly a wild guess, but I have had strange problems with linked tables when using optimistic locking from access. Try changing your .LockType to adLockReadOnly.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

If conConnection is an ADO connection to SQL Server, the SQL you feed it can not use Access/VBA functions which aren't supported by SQL Server. There is no DateValue function in SQL Server.

Upvotes: 1

Tony Toews
Tony Toews

Reputation: 7882

What's the second A doing just after "& "" and before "Inner Join"?

CommandText = "SELECT A.* FROM " & nextMonthTable & " A Inner Join opt_in_customer

I'm no SQL expert but that doesn't look right to me.

Upvotes: 0

Related Questions