煮版老师:
上面的语句,在EXCEL中操纵ACCESS能通过,但是我把连接字符串更改为SQL SERVER,却不能通过了,这个问题困扰好久了.
EXCEL操纵ACCESS原语句:
√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√
'保存和更新基础信息
Sub 保存基础信息()
Dim AdoConn As New ADODB.Connection
Dim strConn As String
Dim DataAddr As String
Dim strSQL As String
'设置连接字符串
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.Path & Application.PathSeparator & _
"工资管理系统数据库.accdb;"
'获取基本信息数据区域地址
DataAddr = Sheets("基础信息").Range("A3:J" & Sheets("基础信息").Range("A" & Cells.Rows.Count).End(xlUp).Row).Address(0, 0)
'打开连接
AdoConn.Open strConn
'删除数据库中的原有记录
strSQL = "Delete * From [基础信息] Where 员工编号 in (Select 员工编号 From" & _
"[Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[基础信息$" & _
DataAddr & "] Where Not 员工编号 Is Null)"
'执行SQL语句
AdoConn.Execute strSQL
'设置SQL上传语句
strSQL = "Insert Into [基础信息] Select * From [Excel 12.0;HDR=YES;DATABASE=" & _
ThisWorkbook.FullName & "].[基础信息$" & DataAddr & "] Where Not 员工编号 Is Null"
'执行上传语句
AdoConn.Execute strSQL
'将保存完毕的基础信息表再次导入到工作表中以更新信息
strSQL = "Select 员工编号,姓名,部门,身份证号码,基本工资,养老保险,医疗保险,失业保险,公积金,在职状态 From [基础信息] Order By 部门"
Sheets("基础信息").Range("A4").CopyFromRecordset AdoConn.Execute(strSQL)
'关闭数据库连接
AdoConn.Close
'提示信息
MsgBox "保存完毕"
End Sub
√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√√
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
但是更改SQL SERVER字符串,更改相关SQL语句,不能通过,提示WHERE附近有语法错误
strConn = "Provider=SQLOLEDB;" & _
"Data Source=172.16.216.1;" & _
"Initial Catalog=sql2;" & _
"User id=SA;" & _
"Password=123456;"
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
