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
