I have done a partial conversion of a large MS Access batch processing
program to use a SQL server 2000 back end.
All I have done is move all the access tables in to SQL server and
give them primary keys so that tje Access front end can attach to them
as updateable tables. No pass thru queries or anything that will take
advantage of the SQL facilities.
In my development environment I am running Access 97 on Windows 98.
The client is running Access 97 on Windows XP
I am seeing different behaviour between my environment and the clients
environment - with exactly the same data sitting on the back end SQL
databases and exactly the same Access 97 front end.
I have managed to knock all the bugs out of the program when it runs
in my environment - but in their environment we are still seeing the
dreaded "Record is Deleted" message - seems to occur in some
situations where I use an outer join and attempt to pick up a value
from a field in the non existent record side of the join.
QUESTION - presumably the discrepancies are caused by us running
different versions of MDAC
1) can anyone tell me where I can download that utility that tells you
which version of mdac you are running
2) which part of mdac am I using when access attaches to the SQL back
end but all the work is still being done by the access front end ?
ole db server for Access ? odbc driver for SQL, jet ?
Many thanks
TonyHi
I am not an expert in this but..
> QUESTION - presumably the discrepancies are caused by us running
> different versions of MDAC
> 1) can anyone tell me where I can download that utility that tells you
> which version of mdac you are running
Down load the component check from
http://msdn.microsoft.com/data/mdac/default.aspx
> 2) which part of mdac am I using when access attaches to the SQL back
> end but all the work is still being done by the access front end ?
>
As you are not using passthru queries you may be doing most of the work at
the front end. You will be using the Jet engine to connect to your access
database and (probably) the oledb driver for SQL Server to connect to the
server (unless you are doing it though ODBC).
> ole db server for Access ? odbc driver for SQL, jet ?
> Many thanks
> Tony
John|||(1) MDAC Component Checker
http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en
(2) I would guess that you are using the ODBC driver, but I don't know. How
did you set up the link to SQL Server in the first place?
--
Keith
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41c9a201.57817078@.news.m.iinet.net.au...
> I have done a partial conversion of a large MS Access batch processing
> program to use a SQL server 2000 back end.
> All I have done is move all the access tables in to SQL server and
> give them primary keys so that tje Access front end can attach to them
> as updateable tables. No pass thru queries or anything that will take
> advantage of the SQL facilities.
> In my development environment I am running Access 97 on Windows 98.
> The client is running Access 97 on Windows XP
> I am seeing different behaviour between my environment and the clients
> environment - with exactly the same data sitting on the back end SQL
> databases and exactly the same Access 97 front end.
> I have managed to knock all the bugs out of the program when it runs
> in my environment - but in their environment we are still seeing the
> dreaded "Record is Deleted" message - seems to occur in some
> situations where I use an outer join and attempt to pick up a value
> from a field in the non existent record side of the join.
> QUESTION - presumably the discrepancies are caused by us running
> different versions of MDAC
> 1) can anyone tell me where I can download that utility that tells you
> which version of mdac you are running
> 2) which part of mdac am I using when access attaches to the SQL back
> end but all the work is still being done by the access front end ?
> ole db server for Access ? odbc driver for SQL, jet ?
> Many thanks
> Tony|||On Wed, 22 Dec 2004 17:24:03 -0000, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>> 2) which part of mdac am I using when access attaches to the SQL back
>> end but all the work is still being done by the access front end ?
>You will be using the Jet engine to connect to your access
>database and (probably) the oledb driver for SQL Server to connect to the
>server (unless you are doing it though ODBC).
Here is the code snippet I am using (the SQL case) (many thanks for
previous assistance in this area)
I have never really understood which driver I am using in this code -
but would be nice to know if I am looking at mdac version issues.
Could I ask someone to please eyeball my code and tell me which part
of mdac is being used.
For this routine, in the SQL case, the paramter "AttachPath" holds the
server name and database name (eg
"DATABASE=WFimporterWork;SERVER=SQLTESTSVR2;")
- I am doing a DSNless connection
Many thanks
Tony
Sub AttachATable(AttachPath, TableName, AttachName, strDatabaseType,
varUid As Variant, varPwd As Variant)
Dim MyTableDef As TableDef
Set MyTableDef = currentDb().CreateTableDef()
Dim strConnect As String
Select Case strDatabaseType
Case "SQL"
strConnect = "ODBC;DRIVER={sql server};" & AttachPath
& "TABLE=" & TableName & ";Trusted_Connection=No;"
If IsNull(varUid) = False Then
strConnect = strConnect & "UID=" & varUid & ";"
End If
If IsNull(varPwd) = False Then
strConnect = strConnect & "PWD=" & varPwd & ";"
End If
MyTableDef.Connect = strConnect
MyTableDef.Attributes = dbAttachSavePWD
Case "MDB"
MyTableDef.Connect = ";DATABASE=" + AttachPath
AttachName = AttachName & "_mdb"
End Select
MyTableDef.SourceTableName = TableName
MyTableDef.Name = AttachName
currentDb().TableDefs.Append MyTableDef ' Attach table.
End Sub|||Hi
ODBC is part of MDAC and you are using the SQL Server ODBC driver.
John
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41ca1245.86557359@.news.m.iinet.net.au...
> On Wed, 22 Dec 2004 17:24:03 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
>
>> 2) which part of mdac am I using when access attaches to the SQL back
>> end but all the work is still being done by the access front end ?
>>You will be using the Jet engine to connect to your access
>>database and (probably) the oledb driver for SQL Server to connect to the
>>server (unless you are doing it though ODBC).
> Here is the code snippet I am using (the SQL case) (many thanks for
> previous assistance in this area)
> I have never really understood which driver I am using in this code -
> but would be nice to know if I am looking at mdac version issues.
> Could I ask someone to please eyeball my code and tell me which part
> of mdac is being used.
> For this routine, in the SQL case, the paramter "AttachPath" holds the
> server name and database name (eg
> "DATABASE=WFimporterWork;SERVER=SQLTESTSVR2;")
> - I am doing a DSNless connection
> Many thanks
> Tony
> Sub AttachATable(AttachPath, TableName, AttachName, strDatabaseType,
> varUid As Variant, varPwd As Variant)
> Dim MyTableDef As TableDef
> Set MyTableDef = currentDb().CreateTableDef()
> Dim strConnect As String
> Select Case strDatabaseType
> Case "SQL"
> strConnect = "ODBC;DRIVER={sql server};" & AttachPath
> & "TABLE=" & TableName & ";Trusted_Connection=No;"
> If IsNull(varUid) = False Then
> strConnect = strConnect & "UID=" & varUid & ";"
> End If
> If IsNull(varPwd) = False Then
> strConnect = strConnect & "PWD=" & varPwd & ";"
> End If
> MyTableDef.Connect = strConnect
> MyTableDef.Attributes = dbAttachSavePWD
> Case "MDB"
> MyTableDef.Connect = ";DATABASE=" + AttachPath
> AttachName = AttachName & "_mdb"
> End Select
> MyTableDef.SourceTableName = TableName
> MyTableDef.Name = AttachName
> currentDb().TableDefs.Append MyTableDef ' Attach table.
> End Sub
