Unforeseen problem?

[Ausburgh]

I created a very simple application in VB 6.0 (a couple of you helped straighten me out a few times) with an MS Access back-end for my local agency.

But now there's an opportunity for our sister agencies around the country to adopt the applications too. So we decided to put in on the SQL server (to help with access rights and system backups, etc.) So yesterday I upsized the Access database to SQL server.

But here are my main concerns:

1. Will the fact that I used DAO from VB to connect to Access become a problem?
2. In conjunction, is it necessary to switch to ADO?
3. What can I do to make possible for multiple people to write to the database at the same time?

Thanks in advance for your help.

 

[zemp]

DAO is pretty much specific to the Access Jet engine. I would recommend converting to ADO.

Multiple users is mostly handled by SQL Server. However you will need to manage record locking though ADO so that no two users try to update the same record at the same time. ADO lock and cursor types allow you to handle this.

Also be aware of SQL Injection (faq709-1526).

zemp

 

[Ausburgh]

Thanks zemp for replying.

So inspite of the .adp Access file (that was created when I upsized) file I still need ADO?

Thanks again

 

[Sheco]

The adp is fundamentally different from an Access mdb.

The .adp is merely a mechanism used to send commands to the server and display the results in a familiar format. Take a look at the SQL Server Enterprise Manager and also at the Query Analyzer... these apps also send commands to SQL Server and display the results... you could use the Query Analyzer to do all of the tasks normally done in the Enterprise Manager and vice versa... the difference is that the layout and presentation in Query Analyzer is focused on writing and optimizing SQL statements while the Enterprise Manager is focused on server management.

So although you *could* use the Query Analyzer to do something like restore a database from a backup file and you *could* use the Enterprise Manager to write a new view or stored procedure, it is best to use the tool specifically crafted for the job.

I hope that made a little bit of sense.

 

[Ausburgh]

Thanks Sheco.

I understand it now.

 

[Ausburgh]

I'm Switching all my DAO controls to ADO - but running into a problem.

Can someone tell me why each first field in all the first records in my tables empty now?

Thanks.

 

[zemp]

Could be an off by one error.

In other words if you are using the field index values You may have be starting at index 1 when now you now need to start at index 0 (zero). Check which starting index is used by DAO and ADO respectivly. I believe ADO starts at zero (I have been using the field names rather than the index for so long that I am not 100% sure anymore).

zemp

 

[Ausburgh]

Nope that didn't work.

Even after I change it and I close the application - once I start it again, the first field is blank again.

 

[Golom]

Exactly how are you determining that it's blank?

Is it being displayed in a grid or some other control?

Are you refering to a recordset field in code?



[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Ausburgh]

I used the ADODC control: Connection String: DSN=dbFSF

Recordsource is pointing to the table (Allocation) in dbFSF
------------------
Whenever I run the application, the first field (of the first record only) which happens to be our "Branch" field is just empty. Even when I input the correct "Branch" into the blank field, save it and close out the form -- the next time I open the form the same thing happens again.

 

[Golom]

... and you are using the ADODC.Recordset as the Recordsource for an ADO Datagrid?

Unlike DAO, the ADO Datagrid can use an ADODB.Recordset as its recordsource. That seems to create fewer problems.

I've always found that the ADO Data Control to be rather flakey and unpredictable as a recordsource for other controls.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Ausburgh]

It sounds like I'm using the ADODB.Recordset as its recordsource (image, this is my FIRST project in VB/ACCESS/SQL too -- trying to learn as I go)

What should I do to convert the old DAOs properly? I just need to transition to SQL from Access.

 

[Golom]

Here's a bit of context ...

The ADODC (i.e. the ADO Data Control) is a visible control that you can drop on your screen and it has, among other things, a Recordset property.

The ADO DataGrid control has a DataSource property that identifies a source for the information that will be displayed in the grid.

At design time you can set the "Datasource" property of the Datagrid to an ADO Data Control.

You can also, at run time, set the Datagrid.Datasource property to a recordset with something like

Set DataGrid1.DataSource = rs

where "rs" is an ADODB.Recordset.

Now then ...

Of those two methods it has been my experience that using the ADODC (i.e. the ADO Data Control) as the Datasource is problematic. It does work but is fairly inflexible and may be prone to problems such as you are experiencing.

Using an ADODB.Recordset as the Datasource (assigned at run time) seems to be more stable and gives better results (again, in my experience.)

Note also that, when switching from DAO to ADO, you may need to use different controls. A DAO DataGrid is incompatible with an ADO datasource. For ADO you need to be using the
Microsoft Data Grid Control (SP6) (OLEDB) and NOT the
Microsoft Data Bound Grid Control 5.0 (SP3) which is the DAO-compatible control.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Ausburgh]

I went with the code below and I plan on calling it with: Private MyDBConn As ASource.

However I'm not so sure on how to connect each field (from VB) to the fields in SQL server. I have 25 fields I need to link to the SQL server DB, Any suggestions?


My Code (in Class):
------------------------------
------------------------------

Option Explicit

Dim conFSF As ADODB.Connection ' Create ADODB connection object
Dim rsBudget As ADODB.Recordset ' Create ADODB recordset object
Dim adoErrors As ADODB.Errors ' Create ADODB error object


'****************************************************************************
'** PRIVATE PROCEDURES **
'****************************************************************************

Private Sub Class_Initialize()
'* Automatic upon instantiation by calling module
On Error GoTo HandleError

Set conFSF = New ADODB.Connection 'bind and define the DB connection
conFSF.ConnectionString = _
"Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=dbFSF;" & _
"Data Source = AFC - FM45237"
conFSF.Open 'open the DB connection

Dim strSQL As String 'define the recordset access statement
strSQL = "SELECT * Allocation"

Set rsBudget = New ADODB.Recordset 'bind, define, and open the recordset
With rsBudget
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strSQL, conFSF, adOpenDynamic, adLockOptimistic, adCmdText
End With

Class_Initialize_Exit:
Exit Sub

HandleError:
ADOerror

End Sub

Private Sub Class_Terminate()
'* Release resources
Set conFSF = Nothing
Set rsBudget = Nothing
Set adoErrors = Nothing
End Sub


Private Sub ADOerror()
'/ Enumerate the Errors collection and display properties of each Error object
Dim errCollection As Variant
Dim errLoop As Error
Dim strError As String
Dim iCounter As Integer

On Error Resume Next '/ in case ADO connection not set or other init problems
iCounter = 1
strError = " "
For Each errLoop In errCollection
With errLoop
strError = _
"Error #" & iCounter & vbCrLf & _
" ADO Error #" & .Number & vbCrLf & _
" Description - " & .Description & vbCrLf & _
" Error Source - " & .Source & vbCrLf
Debug.Print strError
iCounter = iCounter + 1
End With
Next
End Sub
-------------------------------
--------------------------

Am I on the right path?

Thanks