From Jon: Fountainhead Software Solutions, Canby
Oregon
Forgive the lengthy message here but I have so
many questions, issues, and experiences I don't know where to start. If you
can answer or address any of these questions please do as one answer may lead
to solving many more of them. Also, please don't simply reply back with "it
works here" because I'm sure it does work other places, I just need to know
how to get to that place.
I have developed several systems using VB6
initially tied to MS Access db's using DAO. I converted one of these systems a
year and a half ago to work with MS Sql Server using ADO. This was
after trying for a couple of weeks to get it to work well with MySQL. I
was able to make that work, but performance in the MS environment using
VB6 & ADO was very sluggish and everything I read warned me of that MySQL
just wasn't ready for this environment. I recently converted another system
likewise to MS Sql Server only to find they balked at spending the
thousands of dollars in Microsoft licenses. I happened across SAPDB last week
and am confident that it will prove to be the answer as I'd like to get out of
Microsoft db products all together.
Here's been my experience with
SAPDB:
I successfully installed the server, the test
database, SQL Studio, the DB Manager GUI, the SAPDB ODBC package. I read
User Guide pages and Repmcli pages and User Reference pages on my computer
screen until my eyes hurt, bad. (I sure wish I could find a printed book on
this stuff in English) I was able to get MS Access to link tables I created
via ODBC and I was able to add records and do most anything you'd expect. I
was able to create a VB6 program that used the ADO data control, added it to
an Infragistics grid and was merrily able to manipulate data to my hearts
content. I figured I was ready to tackle porting the data from the MS Access
db to SAPDB. I decided to do a little test.
This is where the effort jumped off the
track.
Using repmcli I was able to import data into a
table from a csv file providing the file only had one field. Once I added a
second field repmcli complained that it couldn't find data for the second
field. After scouring the archives I stumbled upon the information that I
needed to have all fields encased in double quotes whether they were text
fields or not. Hmmm. that's a pain but I can deal with it. Once I was able to
add more than one column via repmcli I decided to make a table with all the
data types I'd have to deal with.
Here's how I figured I'd have to convert from MS
Access types to SAPDB types: number(long) -> integer, date/time -> date,
text -> varchar, memo -> long(ascii), currency -> float. Do you see
any potential problems with this? I'm particularly nervous about the data/time
fields and memo field conversions.
I built a table using all these types. I created
a csv file with all fields enclosed in double quotes by changing all of my
Access fields to type text so Access would export the data with the double
quotes. The data file looked to me exactly as I'd want it. Despite this I
could never get repmcli to get past the first line of data as I kept getting
the No data supplied error message on one of the memo fields. Figuring I was
now creating more problems than I was solving with all these gyrations I
decided to abandon repmcli and search for another method.
I tried Microsoft DTS. It looked very promising
as I was able to connect using ODBC to both the Access table and the SAPDB
table that I was to move data from and to. When I clicked on the transfer
button it all looked like it was going to work. Hours later it was obvious it
wasn't working as nothing had changed and it should've only taken a couple of
minutes. I finally killed the DTS process. I connected to the SAPDB
database using SQL Studio (as I had many time before), but now when I tried to
look at any table using ALTER Table, SQL Studio would lock up. There had been
no records added to the SAPDB table either. I performed a complete backup of
the database as I'd read that this was necessary after using repmcli to get it
out of readonly mode, thinking maybe this was the same issue. In the end there
was nothing I could do the salvage this situation and decided to create a new
test database.
HERE's a question: how do I get rid of these test
database I seems to be accumilating on my server so that it appears they never
existed?
So I created a new database and another new
table. I decided I'd write a VB6 program to simply transfer data from Access
using ADO recordsets. Here's the code:
Dim strSQL As
String
Dim i As Long
Dim rsO As
New ADODB.Recordset
Dim cnO As New
ADODB.Connection
Dim rsS As New
ADODB.Recordset
Dim cnS As New
ADODB.Connection
cnO.CursorLocation = adUseClient
cnO.Open
"DSN=OriginalData;"
cnS.CursorLocation = adUseClient
cnS.Open
"DSN=SAPDBTst1;Password=DMSPWD;User ID=DMS;"
strSQL =
"select * from tbltst1Export order by ProjId"
rsO.Open
strSQL, cnO, adOpenForwardOnly, adLockReadOnly
strSQL = "select * from
ATBLProject"
rsS.Open strSQL, cnS, adOpenStatic,
adLockBatchOptimistic
Do While Not
rsS.EOF
Debug.Print
rsS.Fields("TITLE")
rsS.MoveNext
Loop
rsS.MoveFirst
Do While Not
rsO.EOF
rsS.AddNew
For i = 0 To
rsO.Fields.Count -
1
rsS.Fields(rsO.Fields(i).Name) =
rsO.Fields(i)
Next
i
rsS.Update
rsO.MoveNext
Loop
With this code I was able to APPARENTLY move all
the data from the Access table to the SAPDB table as I got NO ERRORS. When I
went to SQL Studio to look at the data none was added. When I used ADO to look
at the data nothing appeared. I then linked to the SAPDB table using Access
and added 1 record. This one record showed up but I never was able to add data
to the table using an ADO recordset.
THE QUESTION MOST IMPORTANT TO ME is why didn't
the above code work? Why didn't the data get added to the SAPDB table and why
didn't I get any error messages when it didn't work?!??!? This one is really
disturbing.
I then went to MS Access again, linked the SAPDB
table. Created an append query to move data from the Access table to the SAPDB
table. this query locked up as well. I ended up killing the Access
process. Now the new test database appears to be toast just like the last
one. Why does this keep happening?
I'd really like to use SAPDB and knew there'd be
a learning curve just as there was for SQL Server and MySQL. But in my
experience one worked for me (SQL Server) and one didn't work (MySQL) for
me. I need to decide quickly if SAPDB will work for me. Any guidance anyone
can give me to help me through this crawling over broken glass period would be
greatly appreciated. Or simply being told that what I'm trying to do just
ain't going to work would also be usefull.
Cheers,
Jon
