I think I've found the bug in Your VB Code - and think,
that it is the reason why it doesn't work.
Take a look at the destination recordset (rs)
You open it with a line:
rsS.Open strSQL, cnS, adOpenStatic, adLockBatchOptimistic
Pay attention to adLockBatchOptimistic.
It is a special mode for opening recodset in batch mode.
In this mode no data is saved to server until You execute
rsS.UpdateBatch
Since You are trying to save data with standard Update method
no data is written to Your table. Am I right?
Try using adLockOptimistic insead.
I use SAP DB for more than 1 year, with client
application which is a VB 6 program and it works very well.
Best regards,
Marcin Pytel
TALEX S.A. Poznan
Uzytkownik "jd" <[EMAIL PROTECTED]> napisal w wiadomosci
004301c2a15d$66551e40$6401a8c0@fhswsxp01">news:004301c2a15d$66551e40$6401a8c0@fhswsxp01...
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
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
