petedw
03-13-2006, 04:08 AM
Hi guys,
The following code is part of something that i use to talk to Access from Word (Users fill in a userform, click the command button and the info is put into a database). I now need to use an SQL Database instead of Access and i am wondering what i will need to change in my code.
I hope you can help.
Many Thanks
Pete
Public Sub UserForm_Initialize()
'*** This sub runs on open. It fills the Cust Ref dropdown box to allow
'*** auto complete to work for cust ref box
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = OpenDatabase(strStartFolder & strCustRefDatabase)
Set rst = dbs.OpenRecordset("Select CustomerRef FROM Customer;")
Do While Not rst.EOF
Me.cboCustomerRef.AddItem rst("CustomerRef")
rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
End Sub
Sub FillDatabase()
Dim cn As New ADODB.Connection:
Dim rst As New ADODB.Recordset
Dim oCtl As MSForms.Control:
Dim sPath As String
On Error GoTo FillDatabase_Error
sPath = strStartFolder & strCustRefDatabase
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open sPath
rst.Open "Customer", cn, adOpenKeyset, adLockOptimistic
If tbAddress3.Text = "Address Line 3" Then tbAddress3.Text = ""
If tbMeterPoint.Text = "Meter Point Reference" Then tbMeterPoint.Text = ""
If tbNotes.Text = "Enter Notes Here (E.G. Print Onto Green Paper)" _
Then tbNotes = ""
Field_CustomerRef = cboCustomerRef.Text
Field_CustomerName = tbLongName.Text
Field_CustomerAd1 = tbAddress1.Text
Field_CustomerAd2 = tbAddress2.Text
Field_CustomerAd3 = tbAddress3.Text
Field_Postcode = tbPostCode.Text
Field_Shortname = tbShortName.Text
Field_Meterpoint = tbMeterPoint.Text
Field_Inputter = ActiveDocument.BuiltInDocumentProperties("Author").Value
Field_Notes = tbNotes.Text
rst.AddNew Array(FIELDNAME_1, _
FIELDNAME_2, _
FIELDNAME_3, _
FIELDNAME_4, _
FIELDNAME_5, _
FIELDNAME_6, _
FIELDNAME_7, _
FIELDNAME_8, _
FIELDNAME_9, _
FIELDNAME_10), _
Array(CVar(Field_CustomerRef), _
CVar(Field_CustomerName), _
CVar(Field_CustomerAd1), _
CVar(Field_CustomerAd2), _
CVar(Field_CustomerAd3), _
CVar(Field_Postcode), _
CVar(Field_Shortname), _
CVar(Field_Meterpoint), _
CVar(Field_Inputter), _
CVar(Field_Notes))
rst.Update
MsgBox "Customer Record Saved"
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
Exit Sub
FillDatabase_Error:
If Not rst Is Nothing Then
If rst.State = adStateOpen Then
rst.Close
End If
End If
Set rst = Nothing
If Not cn Is Nothing Then
If cn.State = adStateOpen Then
cn.Close
End If
End If
Set cn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error!"
End If
End Sub
The following code is part of something that i use to talk to Access from Word (Users fill in a userform, click the command button and the info is put into a database). I now need to use an SQL Database instead of Access and i am wondering what i will need to change in my code.
I hope you can help.
Many Thanks
Pete
Public Sub UserForm_Initialize()
'*** This sub runs on open. It fills the Cust Ref dropdown box to allow
'*** auto complete to work for cust ref box
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = OpenDatabase(strStartFolder & strCustRefDatabase)
Set rst = dbs.OpenRecordset("Select CustomerRef FROM Customer;")
Do While Not rst.EOF
Me.cboCustomerRef.AddItem rst("CustomerRef")
rst.MoveNext
Loop
Set rst = Nothing
Set dbs = Nothing
End Sub
Sub FillDatabase()
Dim cn As New ADODB.Connection:
Dim rst As New ADODB.Recordset
Dim oCtl As MSForms.Control:
Dim sPath As String
On Error GoTo FillDatabase_Error
sPath = strStartFolder & strCustRefDatabase
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open sPath
rst.Open "Customer", cn, adOpenKeyset, adLockOptimistic
If tbAddress3.Text = "Address Line 3" Then tbAddress3.Text = ""
If tbMeterPoint.Text = "Meter Point Reference" Then tbMeterPoint.Text = ""
If tbNotes.Text = "Enter Notes Here (E.G. Print Onto Green Paper)" _
Then tbNotes = ""
Field_CustomerRef = cboCustomerRef.Text
Field_CustomerName = tbLongName.Text
Field_CustomerAd1 = tbAddress1.Text
Field_CustomerAd2 = tbAddress2.Text
Field_CustomerAd3 = tbAddress3.Text
Field_Postcode = tbPostCode.Text
Field_Shortname = tbShortName.Text
Field_Meterpoint = tbMeterPoint.Text
Field_Inputter = ActiveDocument.BuiltInDocumentProperties("Author").Value
Field_Notes = tbNotes.Text
rst.AddNew Array(FIELDNAME_1, _
FIELDNAME_2, _
FIELDNAME_3, _
FIELDNAME_4, _
FIELDNAME_5, _
FIELDNAME_6, _
FIELDNAME_7, _
FIELDNAME_8, _
FIELDNAME_9, _
FIELDNAME_10), _
Array(CVar(Field_CustomerRef), _
CVar(Field_CustomerName), _
CVar(Field_CustomerAd1), _
CVar(Field_CustomerAd2), _
CVar(Field_CustomerAd3), _
CVar(Field_Postcode), _
CVar(Field_Shortname), _
CVar(Field_Meterpoint), _
CVar(Field_Inputter), _
CVar(Field_Notes))
rst.Update
MsgBox "Customer Record Saved"
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
Exit Sub
FillDatabase_Error:
If Not rst Is Nothing Then
If rst.State = adStateOpen Then
rst.Close
End If
End If
Set rst = Nothing
If Not cn Is Nothing Then
If cn.State = adStateOpen Then
cn.Close
End If
End If
Set cn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error!"
End If
End Sub