from access to sequel Server

[jtm7699]

We started out with the data in an access DB

Now it is on a sequel server

i am using the data environment


running

 linkname.tablename

name opened the table when using access

now when using SQL server i get error
runtime error -2147217900(80040e14)
Line 1: incorrect syntax near '`'

what is it talking about and to get around it?

[JHausmann]

What access methodology were you using (DAO, ADO, ODBC)? If you were using DAO, you'll need a different way of getting to SQL Server (unless you want to create an Access database that contains linked tables to SQL Server).

[Edited by JHausmann on 08-01-2000 at 04:36 PM]

[jtm7699]

i am using ADO.

I am linking direct to the SQL server.

you can not link to the tables that are linked to by Access, they do not appear through the data environment.

i think that i am part way there, now I need to find out how to pass variables to the SQL statements, since the syntax in slightly different then in access

[JHausmann]

Like statements will use a '%' instead of '*'.

Date queries are not wrapped with the # sign in SQL server, they are wrapped with quotes.

Anybody think of anything else?

[jtm7699]

but how do i pass in a parameter



this is what i have now

SELECT dbo.Schedule.ASC_ApptDate, dbo.Schedule.ASC_ACL_ID,
dbo.Schedule.ASC_AAU_ID, dbo.Schedule.ASC_QtrID,
dbo.Audiologist.AAU_Initials, dbo.Schedule.ASC_Status,
dbo.Audiologist.AAU_FirstName,
dbo.Audiologist.AAU_LastName
FROM dbo.Audiologist INNER JOIN
dbo.Schedule ON
dbo.Audiologist.AAU_ID = dbo.Schedule.ASC_AAU_ID
WHERE (dbo.Schedule.ASC_ApptDate BETWEEN [start] AND
DATEADD(d, 6, [start])) AND (dbo.Schedule.ASC_ACL_ID = N' ')
AND (dbo.Schedule.ASC_Status = N'C')


tells me Start is an invalid column name

[JHausmann]

Start appears to be an access variable. You can replace it with a vb variable (of course, you now have to figure out how to set the variable in VB).


dim dtStart as date
dim sSql as string

sSQL = "SELECT dbo.Schedule.ASC_ApptDate, dbo.Schedule.ASC_ACL_ID, dbo.Schedule.ASC_AAU_ID, dbo.Schedule.ASC_QtrID, dbo.Audiologist.AAU_Initials, dbo.Schedule.ASC_Status, dbo.Audiologist.AAU_FirstName, dbo.Audiologist.AAU_LastName FROM dbo.Audiologist INNER JOIN dbo.Schedule ON dbo.Audiologist.AAU_ID = dbo.Schedule.ASC_AAU_ID WHERE (dbo.Schedule.ASC_ApptDate BETWEEN '" & dtStart & "' AND '" & DATEADD(d, 6, dtStart)) &"' AND (dbo.Schedule.ASC_ACL_ID = N' ')
AND (dbo.Schedule.ASC_Status = N'C') "

'then you set the data environment to sSQL

[jtm7699]

i read a previous one incorrectly. We are using ODBC to get to the Sequel Server.

setting a variable is easy. When we had access links, I use to write the SQL and "start" was a variable that i could pass to the SQL,

in this case "Scedule.allappointments gstartdate" was used in code


scedule = name of the Data Environement Link
Allappointments = name of the SQL that was written in the Data Environment
gstartdate = the variable that has the date in it that the query needs



now that we have moved to ODBC Sequel Server based DB's instead of Access, the SQL queries no longer work the same. the first query had

SELECT Schedule.ASC_ApptDate, Schedule.ASC_ACL_ID,
Schedule.ASC_AAU_ID, Schedule.ASC_QtrID,
Audiologist.AAU_Initials, Schedule.ASC_Status,
Audiologist.AAU_FirstName, Audiologist.AAU_LastName
FROM Audiologist INNER JOIN
Schedule ON
Audiologist.AAU_ID = Schedule.ASC_AAU_ID
WHERE (Schedule.ASC_ApptDate BETWEEN 'start' AND
DATEADD(d, 6, 'start')) AND (Schedule.ASC_ACL_ID = ' ')
AND (Schedule.ASC_Status = 'C')

the "dbo." was added after I tried to move to SQL server. It was added automatiacally by the Data Environment.


Is there any way to change keep the SQL in the Data Enviroment and not move to code.

[JHausmann]

You could create a table, call it DateStore, that has one entry dtStart of type date. Set the value of that field to the start date for your SQL. Then modify your data environment to use the following SQL

SELECT Schedule.ASC_ApptDate, Schedule.ASC_ACL_ID,
Schedule.ASC_AAU_ID, Schedule.ASC_QtrID,
Audiologist.AAU_Initials, Schedule.ASC_Status,
Audiologist.AAU_FirstName, Audiologist.AAU_LastName
FROM Audiologist INNER JOIN
Schedule ON
Audiologist.AAU_ID = Schedule.ASC_AAU_ID
WHERE (Schedule.ASC_ApptDate BETWEEN (select dtStart from DateStore) AND
DATEADD(dd, 6, (select dtStart from DateStore))) AND (Schedule.ASC_ACL_ID = ' ')
AND (Schedule.ASC_Status = 'C')