trying to convert SQL query to Access, don't know where to start

[timw2979]

I just started working for a company the other week and they threw this query onto my lap with an unrealistic deadline. My programming experience is primarily in VB6, so I am not completely familiar with Access queries.

I was given code that was written for SQL, and the programmer that came up with it is unfamiliar with Access, so I am stuck trying to decipher this. If anyone could help me out, I would greatly appreciate it, as I am thoroughly confused. Here is his original code:


while exists(select top 1 accNum from NoteFile where patindex('%' + char(10) + '%',dbtrref) > 0)
begin
--Insert note into table
insert into #TL19Note(AcctNumber, Note)
select accNum, (case when patindex('%' + char(10) + '%',
dbtrref) <= 0 then dbtrref else left(dbtrref,patindex('%' + char(10) +
'%',dbtrref)) end)
from NoteFile

--Delete note from Note String
update NoteFile
set dbtrref = right(dbtrref,len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) + '%',dbtrref))))
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) > 0

--Delete rows with no more notes
delete from NoteFile
where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) <= 0 or patindex('%' +char(10) + '%',dbtrref) <= 0
end

--Eliminates any that are CRLR or just spaces
delete from #TL19Note
where len(Note) < 10

--Update the date
update #TL19Note
set NoteDate =left(Note,patindex('% %',Note)-1),
Note = right(Note,len(Note)-patindex('% %',Note))

--No '@' means no time?
update #TL19Note
set NoteTime = '0000'
where left(Note,1) <> '@'

--delete the '@' - we know which ones have it because NoteTime is null
update #TL19Note
set Note = right(Note,len(Note)-patindex('% %',Note))
where left(Note,1) = '@'

--Update NoteTime, take time out of note
update #TL19Note
set NoteTime = left(Note,2) + substring(note,4,2),
Note = right(Note,len(Note)-patindex('% %',Note))

 

[rac2]

What you have posted is a procedure written in T-SQL, a language used for stored procedures that run in the Microsoft SQL Server database. The equivalent in Access would be a command file written in VBA, Visual Basic for Applications, which is reasonably similar to VB6, so you have a fighting chance once you learn how to create command files in Access. Thats not too difficult.

The T-SQL code is straight-forward.

WHILE EXISTS ()
BEGIN
...
END

This is a loop that will continue as long as the SELECT statement returns rows. Within the loop three SQL statements do some work on rows in the NoteFile table, which presumably will eventually make the looping condition false and the loop will terminate. Talk to your T-SQL guy.

Since you have this procedure to work with, you may also have SQL Server installed in your shop. Install Enterprise Manager and Books Online on your workstation; these will be useful henceforth.

For this particular problem finding the Access functions which correspond to the SQL Server string manipulation functions is the key. PATINDEX(), LEN(), SUBSTRING(), CHAR(), LEFT(), RIGHT() all have equivalents in Access. Use Books Online to learn what they do; use Access Help to find the equivalent.

Books Online is not very useful for learning Transact-SQL, so you might want to run out to the local bookstore and buy the O'Reilly book, Transact-SQL Programming.