Converting a VB/MS-ACCESS app to VB/Oracle

[SteveGlo]

I am familiar with VB/MS-Access applications but know very little about Oracle with a VB front-end.

I believed that if your Oracle database design was 'identical' to your MS-Access database it was a simple matter i.e. changing the connection string to get the VB GUI working against the Oracle database. Would be using ADO by the way.

However an Oracle developer has told me that this is only so if the SQL statements used in the VB code are relatively simple.

Is the complexity of the SQL statements going to be an issue - I am not familiar with 'Oracle SQL extensions' if there is such a thing.

 

[fredericofonseca]

The major problem will be stored procedures if you have any (they have a different name on Access).

Some of the date conversion related functions are also different (names and parameters), so you will also have problems. here.

Why not use Microsoft MSDE (or SQL Server) instead? This would be easier to convert to.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SteveGlo]

Frederico

I didn't go into my problem in tedious detail but basically my organisation doesn't give me any choice. They are standardising on Oracle and culling their Access databases.

Some users have produced some spreadsheets which are badly designed and one of the requirements is to turn them into a database system. I have created some Access tables simply by exporting the worksheets as tab delimited files and then importing these into Access. I am only doing this so I can model the data and design the database structures along RAD lines.

It is apparent that the database will only have a small number of tables and those tables will only have a small number of columns, etc. The record density is very low just a couple of thousand records - in other words an ideal candidate for Access. I thought that if I could create the application as an MS-Access backend with a VB frontend it would be a simple matter to convert it to an Oracle database as the Business demand.

I am also the Project Manager and am facing a ridiculous deadline for deployment - if I do the work I can just about meet the deaadline - if I also have to engage Oracle DBA and Oracle developer, that procurement process is going to mean I fail the deadline. (Yes I really do work for that sort of an organisation).

Anyway an Oracle developer told me that I'll have problems if my SQL is too complex whatever that means - i.e. lots of LEFT JOINS between tables for example. I don't know about Oracle SQL so I just wanted some other advice to help me make a decision about whether my approach is a feasible one or not.

Thanks very much for your advice - it is appreciated.

Steve

 

[fredericofonseca]

Hum...


Ask your Oracle developer what he/she consider a complex SQL query. Joins should not be a problem for Oracle, as I have seem VERY VERY complex queries with Oracle (a few pages long).

As I said before the functions are the problem.
Search on your code for things that are not straight SQL commands , e.g. dateserial and the likes, and whenever you find one go and see if Oracle allows for the same function, either with the same name or with another one.
If you don't have too many of these it whould be easy.


You may wish to try this on your own DB. If your organization policy allows it, install Oracle on your PC and try it yourself without asking for a DBA.

Apart from this there is not much I can do without seeing the code.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SteveGlo]

Frederico

Thanks for your insights - you have given me food for thought. I think that I'll stop at creating the logical database structures and then pass to an Oracle DBA / Developer.

I'm going to have to hold out for some formal Project Management here and not allow myself to be panicked into trying to meet an unreasonable deadline. Oh well at least I'll see what sort of rank I pull in my organisation.

Steve

 

[JaxtheDog]

I have several apps with Oracle and VB6 front end. The major differences are in the structure of the query - not the complexity. There are two things which need to change for Oracle SQL:

First, In Oracle literals need single quotes for example:

Select * from carrier where scac_code='AEID'.

Obviously, this means that you need to handle single quotes in the data string if a literal is entered on the screen. The query

Select * from carrier where name='Sidney's'

will cause an error in Oracle. You need to format it to put a second single quote after the literal single quote before you send the query to oracle.

Select * from carrier where name='Sidney''s'

I use a generic function to do this for all saves and selects. I have put a copy at the end of this reply.

Second, Oracle does joins with a (+) in the where clause rather than the word join, outer join, etc. A good Oracle query book will explaint this in detail.


'=========================================================
Public Function CheckSingleQuote_tb(ByVal sForm As Form, _
ByVal iStartTabIndex As Integer, _
ByVal iEndTabIndex As Integer)
' =========================================================
' Purpose: Converts single quotes (') in textboxes to two single quotes
' - key ASCII is 39 for single quote (')
' Requires: Start- and End-Tab index of controls be continuous sequence of #s

Dim ctrl As Control

For Each ctrl In sForm.Controls
If TypeOf ctrl Is Image Then GoTo Skip_Control

Select Case ctrl.TabIndex
Case Is < iStartTabIndex
' continue with loop
Case Is > iEndTabIndex
' continue with loop
Case Else
If TypeOf ctrl Is TextBox Then
Dim LengthToStop As Long ' location of single quote (': ASCII 39)
Dim DataLength As Long ' length of textbox string
Dim Start As Long ' start position of search string

' may need to check for & remove repeating single quotes (eg: '''')
' double quotes (": ASCII 34) are OK!
' set initial starting position of search in string to 1
Start = 1

Find_Next:
' set/reset length of string in textbox
DataLength = Len(ctrl)
' locate position of single quote (')
LengthToStop = InStr(Start, ctrl, "'")

If LengthToStop = 0 Then
' done: move on

ElseIf LengthToStop > 0 Then
' add another single quote -- not a double quote
ctrl = Left(ctrl, LengthToStop) _
& "'" _
& Right(ctrl, (DataLength - LengthToStop))

' set start of next search to position after SECOND (added) single quote!
Start = LengthToStop + 2

' repeat until entire string searched for single quotes (')
GoTo Find_Next

End If

Else
' skip over these controls
End If

End Select

Skip_Control:
Next ctrl

End Function

 

[petermeachem]

Replace(cinp, "'", "''") ?

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[Swi]

I don't think VB 5 had a Replace funtion so something like JaxtheDog's example would be of use if the app was in VB 5.

Swi

 

[ZOR]

Dont know if there are any useful bits in here. I have a similar dark cloud comming along where a user is going over to Oracle, so I could be in the same boat.
Good Luck.

 

[JaxtheDog]

Peter, you are right of course, your replace example is quicker (and shorter). This is code I have used for quite a while. The point is to replace any single quote in the data with two (and only two) single quotes both in the where clause and in any data which is being saved to Oracle or you will get an error. You need to check each field on the data entry screen to make sure you are not saving or requesting bad data.

Zor, Despair not! Using Oracle with VB is actually pretty straight forward once you get past a few quirks in how Oracle formats a SQL query. Most Intro to Oracle books tell you about the differences up front.

Jax

 

[ZOR]

Thanks Jax. Hope this is not deemed hijacking a thread but thanks for your comment back.