Need some advice on updating MSSQL 2005

[greenguy]

Hi All, I haven't posted in a while so I thought I'd bug you guys. I have data in an Excel document (from SAP) that I send to MSSQL and it works great when I'm at work. The problem is when I travel it's a hit/miss sort of thing for getting the information updated. Sometimes I'll get around 20% of the data loaded when I get a "Not Responding" message. I'm using ADO to load the data. Not sure what would be helpful but here is what I'm using to connect to my database. Like I said this works great in the office but not so hot when I have to VPN. Any thoughts/words of wisdom would be great! Thanks

I posted here because I'm hoping there may be a better way to do this using Access then linking it to MSSQL (but I have no clue).
Option Explicit
Global Const strconn As String = "Driver={SQL Native Client};Server=xxx;Database=xxx;Uid=UserID;Pwd=Password;ConnectionTimeoout=60"
Global cnt As New ADODB.Connection
Global rst As New ADODB.Recordset
Global strSQL As String
Sub Send_WeekMatmasSQL_Data()
Dim cell As Range
Dim MyRange As Range
Dim lrow As Long
Dim strplant As String
Dim strmaterial As String
Dim strmaterialtype As String
Dim strprofitcenter As String
Dim strmrpcontroller As String
Dim strproctype As String
Dim strabc As String
Dim stryearweeknumber As String
Dim lngstock As String
Dim dblcost As Double
Dim lngcount As String
Dim n As Long
Dim lngnumfiles As String
Dim dblpercentdone As Double
Dim strweek As String

On Error GoTo ErrExit
cnt.Open (strconn)

 

[ByteMyzer]

Well, there's your problem. After the line:

cnt.Open (strconn)

...you forgot to write more code! Just finish writing it and I'm sure it will work fine


On a more serious note, you can start by replacing:

Driver={SQL Native Client}

...with:

Driver={SQL Server}

MSSQL2005 will respond just fine with the SQL Server driver. The typical Windows OS install includes the SQL Server driver, but not SQL Native Client. Additionally, if you're using ADO, you should use the following syntax:

Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyUserID;Password=MyPassword;


Perhaps, if you post more of the code past the connection Open statement, we could tell you more?

 

[greenguy]

Thanks for the reply ByteMyzer! Added what you suggested and I'm still gettimg some issues. I took the timeout out as I'm not sure what that should be (I'm a noob). Here is the code in all it's glory. Again, any suggestions are appreciated as I'm trying to teach myself and obviously that's not going so well. And to top it all off I'm now getting a type mismatch error onthe last line of my SQL statement. It looks O.K. but I'm missing something.

Global Const strconn As String = "Driver={SQL Server};Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyID;Password=MyPassword;"
Global cnt As New ADODB.Connection
Global rst As New ADODB.Recordset
Global strSQL As String
Sub Send_WeekMatmas3SQL_Data()
'Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=MyUserID;Password=MyPassword;
Dim cell As Range
Dim MyRange As Range
Dim lrow As Long
Dim strplant As String
Dim strmaterial As String
Dim strmaterialtype As String
Dim strprofitcenter As String
Dim strmrpcontroller As String
Dim strproctype As String
Dim strabc As String
Dim stryearweeknumber As String
Dim lngstock As String
Dim dblcost As Double
Dim lngcount As String
Dim n As Long
Dim lngnumfiles As String
Dim dblpercentdone As Double
Dim strweek As String
On Error GoTo ErrExit
cnt.Open (strconn)
'1504*********************************
Application.ScreenUpdating = False
'In case of failure to upload
strSQL = "DELETE FROM tblcomparison1504 WHERE(Week='2009.25')"
cnt.Execute strSQL
strSQL = "DELETE FROM tblcomparison1506 WHERE(Week='2009.25')"
cnt.Execute strSQL

stryearweeknumber = "2009." & Application.InputBox("What is the week number?", "Week", , , , , , 2) '<----- used for testing
'Range("AZ1").Formula = "=WEEKNUM(today())"<----- used for after current
'strweek = Range("AZ1").Value<----- used for after current
'Range("AZ1").ClearContents<----- used for after current
'stryearweeknumber = Year(Format(Now, "mm/dd/yyyy")) & "wk" & strweek <----- used for testing
'stryearweeknumber = "2009WK" <----- used for testing
frmstatus.Show
'Find last row in matmas3 using the Material field
lrow = Cells(Rows.Count, 2).End(xlUp).Row
'Number of records in For Next Loop
lngnumfiles = lrow - 1
'Set Range to sort
Set MyRange = Range(Cells(2, 2), Cells(lrow, 2))
'Loop through materials
For Each cell In MyRange
cell.Activate
strmaterial = ActiveCell.Value
'Plant
strplant = ActiveCell.Offset(0, -1).Value
strmaterialtype = ActiveCell.Offset(0, 3).Value
strprofitcenter = ActiveCell.Offset(0, 4).Value
strproctype = ActiveCell.Offset(0, 5).Value
strmrpcontroller = ActiveCell.Offset(0, 6).Value
strabc = ActiveCell.Offset(0, 8).Value
lngstock = ActiveCell.Offset(0, 17).Value
dblcost = ActiveCell.Offset(0, 18).Value
strSQL = "INSERT INTO tblcomparison" + strplant + "([Week],[Plant],[Material],[MTyp],[Profit Ctr],[ProcType],[MRP-Cont],"
strSQL = strSQL + "[ABC],[Total Stock])"
strSQL = strSQL + " VALUES('" + stryearweeknumber + "','" + strplant + "','" + strmaterial + "','" + strmaterialtype + "',"
strSQL = strSQL + " + '" & strprofitcenter + "','" & strproctype + "','" + strmrpcontroller + "',"
strSQL = strSQL + "'" + strabc + "'," + lngstock + ")"

cnt.Execute (strSQL)
n = n + 1
dblpercentdone = n / lngnumfiles
frmstatus.lblstatus.Width = (n / lngnumfiles) * frmstatus.Label1.Width
frmstatus.lblnumber.Caption = Format(n / lngnumfiles, "0%")
frmstatus.Repaint
Next cell

cnt.Close
Set cnt = Nothing
Application.ScreenUpdating = True
Unload frmstatus
MsgBox "Update Complete!", vbInformation + vbOKOnly, "Update Complete"
Exit Sub
ErrExit:
Unload frmstatus
MsgBox "Error # " & Err.Number & " (" & Err.Description & ") has occurred.", vbCritical + vbOKOnly, "Error"
End Sub

 

[SOS]

On a more serious note, you can start by replacing:

Driver={SQL Native Client}

...with:

Driver={SQL Server}

Actually, if you are on Windows XP, you should use the SQL Native Client driver for 2005/2008 (actually SQL Native 10 driver for 2008). If on Vista, you can revert to the SQL Server driver. This is directly from Mary Chipman of the Microsoft SQL Server team.

 

[ByteMyzer]

...And to top it all off I'm now getting a type mismatch error onthe last line of my SQL statement. It looks O.K. but I'm missing something. ...

Which line? Is it one of the lines with the DELETE statement? Is it the line with the INSERT statement?

 

[ByteMyzer]

Actually, if you are on Windows XP, you should use the SQL Native Client driver for 2005/2008 (actually SQL Native 10 driver for 2008). If on Vista, you can revert to the SQL Server driver. This is directly from Mary Chipman of the Microsoft SQL Server team.

In the companies for which I've successfully deployed such solutions, they have Windows XP Clients (without SQL Native Client installed), the server uses MS SQL 2005, and the UI uses "Driver={SQL Server}" in the connection string with DAO objects, with no issues.

 

[SOS]

I would set up the SQL string using the ampersand as concatenators for this is in Access and not in SQL Server where you are concatenating. The + concatenation in Access can produce some unintended results.

 

[SOS]

In the companies for which I've successfully deployed such solutions, they have Windows XP Clients (without SQL Native Client installed), the server uses MS SQL 2005, and the UI uses "Driver={SQL Server}" in the connection string with DAO objects, with no issues.

Well, according to Mary, you should be using the SQL Native Client if on XP. Perhaps you've just been lucky and not run into the issues that the use of that driver will prevent. Kind of like saying - "I've been playing Russian Roulette all day and have not gotten shot once yet."

 

[ByteMyzer]

I would set up the SQL string using the ampersand as concatenators for this is in Access and not in SQL Server where you are concatenating. The + concatenation in Access can produce some unintended results.

Like so:

strSQL = "INSERT INTO tblcomparison" & strplant & "([Week],[Plant],[Material],[MTyp],[Profit Ctr],[ProcType],[MRP-Cont],"
strSQL = strSQL & "[ABC],[Total Stock])"
strSQL = strSQL & " VALUES('" & stryearweeknumber & "','" & strplant & "','" & strmaterial & "','" & strmaterialtype & "',"
strSQL = strSQL & "'" & strprofitcenter & "','" & strproctype & "','" & strmrpcontroller & "',"
strSQL = strSQL & "'" & strabc & "'," & lngstock & ")"

 

[ByteMyzer]

Well, according to Mary, you should be using the SQL Native Client if on XP. Perhaps you've just been lucky and not run into the issues that the use of that driver will prevent. Kind of like saying - "I've been playing Russian Roulette all day and have not gotten shot once yet."

SOS,

It's not a question of my having "just been lucky". All the solution I've deployed have been fully tested and validated, and I resent your implication that I am offering irresponsible advice in this thread.

SQL Native Client is not part of the typical installation for Windows XP, which is why I was forced, in the cases I mentioned, to explore the use of the SQL Server Driver when interfacing with MS SQL Server 2005. In each case, all transaction scenarios were tested, validated and signed off, with both SQL Native Client and SQL Server drivers, before the applications were implemented in the respective production environments, with the only reported post-launch issues being due to User errors.

 

[SOS]

SOS,

It's not a question of my having "just been lucky". All the solution I've deployed have been fully tested and validated, and I resent your implication that I am offering irresponsible advice in this thread.

You may have "fully tested" to the best of your ability, but if a member of the Microsoft SQL Server team themselves says USE THE SQL NATIVE CLIENT DRIVER for XP" then I tend to believe them. This is not some other hick saying it. It was former Access and SQL MVP Mary Chipman, who is now a part of the SQL Server Development Team at Microsoft.

SQL Native Client is not part of the typical installation for Windows XP, which is why I was forced, in the cases I mentioned, to explore the use of the SQL Server Driver when interfacing with MS SQL Server 2005. In each case, all transaction scenarios were tested, validated and signed off, with both SQL Native Client and SQL Server drivers, before the applications were implemented in the respective production environments, with the only reported post-launch issues being due to User errors.

So, anyway, as I said before - you may be okay at the moment but there IS a reason why she stressed this. So I will go with it myself whereas you can discount it all you want.

 

[SOS]

And I am giving the OP my statement based on a more authoritative source than you ByteMyzer, regardless of your view of how it is.

 

[ByteMyzer]

And I am giving the OP my statement based on a more authoritative source than you ByteMyzer, regardless of your view of how it is.

This statement in itself is presumptive. You may be sufficiently familiar with your source, but your knowledge of me comes only from what you see of me here in this forum. You do not have visibility on my professional qualifications, experience or accomplishments. Therefore you do not have a proper basis for comparison on how authoritative your source is in comparison with me.

Additionaly, I don't cast aspersions on you when I see you offer advice in this forum which I have cause to question, and I do not appreciate your doing so to me.

 

[greenguy]

Hey guys, sounds like some poo is starting to be thrown. Sorry for sparking a debate here. I am using the "+" because I am writing information to MSSQL 2005 not MS Access. I am using Windows XP and both connection strings have worked. As far as the other problem with the type mismatch, for some reason it doesn't like the dblcost but it bombs out before it even gets a chance to execute the SQL statement. Thanks for all the help!

strSQL = "INSERT INTO tblcomparison" + strplant + "([Week],[Plant],[Material],[MTyp],[Profit Ctr],[ProcType],[MRP-Cont],"
strSQL = strSQL + "[ABC],[Total Stock],[Total Value])"
strSQL = strSQL + " VALUES('" + stryearweeknumber + "','" + strplant + "','" + strmaterial + "','" + strmaterialtype + "',"
strSQL = strSQL + " + '" & strprofitcenter + "','" & strproctype + "','" + strmrpcontroller + "',"
strSQL = strSQL + "'" + strabc + "'," + lngstock + "," + dblcost + ")"

 

[ByteMyzer]

...I am using the "+" because I am writing information to MSSQL 2005 not MS Access...

greenguy,

You may be working with MS SQL 2005, but you are compiling your SQL string in Visual Basic for Applications (VBA), and when concatenating strings in VBA, the ampersand (&) is used, as I posted here.