immediate help needed

[libertygirl]

hi! I need a program in vb that does this as soon as possible, I'd be very grateful if anyone could tell me how to do it:

the customer gives a table containing the old values and new values in an excel sheet which will be connected to an access table.
then the program checks for multiple new values for the same old value and after the user has corrected the values,
shows a form that takes a server and MSSQL database name and after connecting to and opening it, if a value in
it's table(like vouchers_no) is equal with a value from the access table in the oldvalue field, it replaces it's
value with the value of the newfield value of the same record.

[aparnesh]

Your description of the problem is almost incomprehensible. Please specify correctly, using correct grammer and sentences. If You get a reply like this
"take access table then like open sql , and if customer value is correct program put data. if value not correct no data "
will you be able to understand much of it ?

[libertygirl]

Your description of the problem is almost incomprehensible. Please specify correctly, using correct grammer and sentences. If You get a reply like this
"take access table then like open sql , and if customer value is correct program put data. if value not correct no data "
will you be able to understand much of it ?

I'm really sorry! My English in not very good and I was in a real hurry when I wrote that post.

The customers have a table 'vouchers' in their accounting database. They want to to replace the current values of the fields 'voucher_no' with a set of new values. they have the old values and the new values to be assigned in an excel sheet. now I'm supposed to write something that will replace the current values in the 'vouchers' table with the new values from the said excel sheet where the old values match.

The program takes the server and database name to open. I don't know exactly how to do that correctly.

Plus does 'movenext' work on MSSql tables? I get an error when I use it.

Thanks for answering anyway. I apologize again for my poor English

[aparnesh]

You can connect to the Excel sheet using an ODBC connection or using ADO, whichever you feel more comfortable with.

For connecting to SQL server with a server name and database name, you can use an ADO connection. The connection string will contain the Server name, Database Name, User Name and Password. Please look up the correct syntax (I don't have SQL Server, so can't help in that regard. But if you have MSDN, that has got loads of examples for connecting to SQL Server). The Code will be something like

Dim mConn as ADODB.Connection
Set mConn = new ADODB.Connection
mConn.Open <<Connection_String>>

Dim rs as new ADODB.Recordset
rs.Open "Customers",mConn

This will connect to the SQL server as per the <<Connection_String>> value and rs will open the "Customers" table

To update the values from the Excel table, you can use the ADODB Command object. Assuming you have created an ODBC connection to the Excel File called "ExcelConn" and the voucher values are in the named Range 'Voucher_Data', you can use

Dim mExcelConn as new ADODB.Connection
mExcelConn.Open "DSN=ExcelConn"
Dim rsExcel as new ADODB.Recordset
rsExcel.Open "Voucher_Data",mConn

Dim objCmd as new ADODB.Command
set objCmd.ActiveConnection = mConn
Dim mOldVoucherNo as string
Dim mNewVoucherNo as string
Do until rsExcel.EOF
    mOldVoucherNo = rsExcel(0)
    mNewVoucherNo = rsExcel(1)
    objCmd.CommandType = adCmdText
    objCmd.CommandText = "UPDATE VOUCHERS SET VoucherNo = '" & mNewVoucherNo & "' WHERE VoucherNo = '" & mOldVoucherNo & "'"
   objCmd.Execute
   rsExcel.MoveNext
LOOP

set objCmd = nothing
rsExcel.Close
set rsExcel = nothing
mConn.Close
set mConn = nothing

This will take the values from the Excel Sheet named range and replace the old voucher Nos of the VOUCHERS table in SQL Server with new values.

I hope this solves your problem.

As for MoveNext, it works on any recordset irrespective of source (usually). If possible please specify the error you are getting and the corresponding code snippet.

And your English this time was perfect !! :)

[libertygirl]

Thanks a lot!