Questions & Chronicles of someone trying to move from Microsoft DB's to SAPDB

[jd]

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