Tuesday, March 20, 2012
Access/SQL "Write Conflict"
I'm using a Access 97 Front-end and a SQL Server 2000 back-end.
When entering a subform on a form, information has to be saved to the
database.
But when entering the subform I get a "Write conflict" error in Access.
In SQL I'm using 3 tables with 1 view defined on those 3 tables. I use
INSTEAD OF INSERT/UPDATE/DELETE triggers to handle
INSERTS/UPDATES/DELETES.
In Access I use that View as a Source for the form.
Does anyone know why I get this error?
Roy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
When I followed the execution of the code, I saw that the "Write
Conflict" error appeared after the following sub executed:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then If ap_ConfirmAction(Me, apUpdate) = vbNo Then
DoCmd.RunCommand acCmdUndo
'###ROY TOEGEVOEGD VOOR GENEREREN GUID
If IsNull(Me.dummyveld) Then
Set ObjGUID = CreateObject("event.util")
GUID = ObjGUID.GetNewGUID()
Me.dummyveld = Mid(GUID, 2, 36)
Set ObjGUID = Nothing
End If
End Sub
After the "End Sub" the "Write Conflict" error pops up.
I hope someone can help me.
Roy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Access/SQL "Write Conflict"
I'm using a Access 97 Front-end and a SQL Server 2000 back-end.
When entering a subform on a form, information has to be saved to the
database.
But when entering the subform I get a "Write conflict" error in Access.
In SQL I'm using 3 tables with 1 view defined on those 3 tables. I use
INSTEAD OF INSERT/UPDATE/DELETE triggers to handle
INSERTS/UPDATES/DELETES.
In Access I use that View as a Source for the form.
Does anyone know why I get this error?
Roy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!When I followed the execution of the code, I saw that the "Write
Conflict" error appeared after the following sub executed:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then If ap_ConfirmAction(Me, apUpdate) = vbNo Then
DoCmd.RunCommand acCmdUndo
'###ROY TOEGEVOEGD VOOR GENEREREN GUID
If IsNull(Me.dummyveld) Then
Set ObjGUID = CreateObject("event.util")
GUID = ObjGUID.GetNewGUID()
Me.dummyveld = Mid(GUID, 2, 36)
Set ObjGUID = Nothing
End If
End Sub
After the "End Sub" the "Write Conflict" error pops up.
I hope someone can help me.
Roy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Access XP to SQL 7 Linked Tables
c., so that the client (within a WAN) has the GUI front-end that he/she is a
ccustomed to (Access). ODBC seems old and slow, and I wanted to try somethi
ng new - ADO/ADOX/OLEDB? I
s there an easy way to make the connection, iterate through the tables (NOT
the sql system tables) that automatically includes the columns and maintains
that 'link' or connection while the AccessXP GUI front-end is open? This w
ould, of course, need to be
able to support multiple users at the same time.
Right now I get a "Compile error: user defined type not defined" on the fir
st line, but was hoping this was getting close...
THANK YOU!
(On-load event of start-up form.)
Public Function linkTables()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
Dim avarSourceTables() As Variant
avarSourceTables = Array("dbo_tblHandouts", _
"dbo_tblParticipants", _
"dbo_tblSiteNetworks", _
"dbo_tblSubEvent", _
"dbo_tblSubReceivables", _
"dbo_xContacts", _
"dbo_xSites", _
"dbo_xTblEventStatus", _
"dbo_xTblNetwork", _
"dbo_xTblPartStatus", _
"dbo_xTblRate", _
"dbo_xTblRateSubType", _
"dbo_xTblReceivables", _
"dbo_xTblRequestors", _
"dbo_xTblRoomStyle", _
"dbo_xTblReports", _
"dbo_xTblSites", _
"dbo_xTblType")
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=servernameC;" & _
"Database=databasename;" & _
"Trusted_Connection=Yes;" & _
"Uid=oops;" & _
"Pwd=password;"
' Create a new Table object
For i = LBound(avarSourceTables) To UBound(avarSourceTables)
Set oTable = New ADOX.Table
With oTable
.Name = avarSourceTables(i)
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Next i
End Function
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...If you choose to use ADO you need to create your recordsets in code and not
rely on linked tables, which are not supported by ADO...
Steve
"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:uTQj5tO4DHA.1644@.TK2MSFTNGP10.phx.gbl...
quote:
> Basically, I want to 'link' the tables for record modifications, entries,
etc., so that the client (within a WAN) has the GUI front-end that he/she is
accustomed to (Access). ODBC seems old and slow, and I wanted to try
something new - ADO/ADOX/OLEDB? Is there an easy way to make the
connection, iterate through the tables (NOT the sql system tables) that
automatically includes the columns and maintains that 'link' or connection
while the AccessXP GUI front-end is open? This would, of course, need to be
able to support multiple users at the same time.
quote:
> Right now I get a "Compile error: user defined type not defined" on the
first line, but was hoping this was getting close...
quote:
> THANK YOU!
>
> (On-load event of start-up form.)
> Public Function linkTables()
> Dim oCat As ADOX.Catalog
> Dim oTable As ADOX.Table
> Dim sConnString As String
> Dim avarSourceTables() As Variant
> avarSourceTables = Array("dbo_tblHandouts", _
> "dbo_tblParticipants", _
> "dbo_tblSiteNetworks", _
> "dbo_tblSubEvent", _
> "dbo_tblSubReceivables", _
> "dbo_xContacts", _
> "dbo_xSites", _
> "dbo_xTblEventStatus", _
> "dbo_xTblNetwork", _
> "dbo_xTblPartStatus", _
> "dbo_xTblRate", _
> "dbo_xTblRateSubType", _
> "dbo_xTblReceivables", _
> "dbo_xTblRequestors", _
> "dbo_xTblRoomStyle", _
> "dbo_xTblReports", _
> "dbo_xTblSites", _
> "dbo_xTblType")
> ' Set SQL Server connection string used in linked table.
> sConnString = "ODBC;" & _
> "Driver={SQL Server};" & _
> "Server=servernameC;" & _
> "Database=databasename;" & _
> "Trusted_Connection=Yes;" & _
> "Uid=oops;" & _
> "Pwd=password;"
> ' Create a new Table object
> For i = LBound(avarSourceTables) To UBound(avarSourceTables)
> Set oTable = New ADOX.Table
> With oTable
> .Name = avarSourceTables(i)
> Set .ParentCatalog = oCat
> .Properties("Jet OLEDB:Create Link") = True
> .Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
> .Properties("Jet OLEDB:Link Provider String") = sConnString
> End With
> ' Add Table object to database
> oCat.Tables.Append oTable
> oCat.Tables.Refresh
> Next i
> End Function
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Thanks so much for the straight-forward answer Steve. Poop!-is my response.
But, at least I'm not getting things like "You need to make sure you've se
lected the right library." and I keep trying to tweak and search forever.
Thanks again. I'll go back to the odbc file dsn.
Speaking of which, what I thought I could avoid in the first place, I've got
the mdb on a file server and the sql odbc file dsn in the same folder. Two
people can open the file fine, but one gets an odbc failed error with no co
de or description. All are
running same os, same office version, with same version of sql odbc driver (
2000.81.9042.00)
Any clues?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:u01g9bP4DHA.1816@.TK2MSFTNGP12.phx.gbl...
quote:
> Thanks so much for the straight-forward answer Steve. Poop!-is my
response. But, at least I'm not getting things like "You need to make sure
you've selected the right library." and I keep trying to tweak and search
forever.
quote:
> Thanks again. I'll go back to the odbc file dsn.
> Speaking of which, what I thought I could avoid in the first place, I've
got the mdb on a file server and the sql odbc file dsn in the same folder.
Two people can open the file fine, but one gets an odbc failed error with no
code or description. All are running same os, same office version, with
same version of sql odbc driver (2000.81.9042.00)
quote:
> Any clues?
Back when I was doing Access development, then migrated to Access - SQL
Server integration, I always preferred placing Access on the desktop for a
"true" client/server solution. While it takes more time, work, and testing
to get it right you'll have a far more robust solution.
If you have not discovered this book, I highly recommend getting this:
Microsoft Access Developer's Guide to SQL Server
by Andy Baron & Mary Chipman <SAMS>
ISBN: 0672319446
Steve
Sunday, February 19, 2012
Access migration to SQL Server
We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.
Teri Welch
Hi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
John
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:xLuwd.5516$jn.1131@.lakeread06...
> Hello,
> We maintain a VB6 front-end application using an Access 2000 database. All
> code and forms are in VB6. The program also uses several queries/reports
> defined in Access. For corporate reasons we must move to SQL Server right
> away. Based on our configuration, we're hoping someone can give us a feel
> for what's involved here. Since we're only using Access for the data we're
> not sure what an "upsizing" would entail. Can someone point us in the
> right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch
>
|||There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.
Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.
What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.
With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...
http://groups.google.com/groups?hl=e...ases.ms-access
Ryan
Teri Welch wrote:
> Hello,
> We maintain a VB6 front-end application using an Access 2000
database. All
> code and forms are in VB6. The program also uses several
queries/reports
> defined in Access. For corporate reasons we must move to SQL Server
right
> away. Based on our configuration, we're hoping someone can give us a
feel
> for what's involved here. Since we're only using Access for the data
we're
> not sure what an "upsizing" would entail. Can someone point us in the
right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch
|||See if this is useful............
"Moving Your Access 2002 Database to SQL Server"
http://msdn.microsoft.com/sql/defaul...sqlbackend.asp
Thanks
GYK
|||On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi
>If you are going to remove access completely your access queries will need
>to be re-written as stored procedures or views and the reports rewritten in
>(say) VB. Alternatively you can use an Access Data Project where most of the
>code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
>The access upgrade wizard will migrate most things, although it can run into
>problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
>I suggest that you create a test system and copy your database and
>application onto it. Then you try the upgrade, and you will get a feel for
>what is involved and how much can be easily migrated.
An excellent suggestion - I agree.
|||Hi Steve
I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.
John
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>
> I have to say it - bah! There - I said it.
> Every single solitary time I've seen someone post on Usenet asking about
> upsizing an Access database to SQL Server, the first response says
> something
> about having to convert all your queries to views and stored procedures.
> Frankly, that's not at all necessary, and a serious drain on time and
> money.
> Access/DAO mostly does a fine job of translating queries of linked tables
> into
> reasonable server-side SQL for execution, and it creates prepared
> statements
> for parameter queries, so SQL Server won't have to keep recompiling each
> query
> each time it sees it. Furthermore, Access is quite happy doing things
> like
> using a named query parameter to look up a value from a form control and
> allowing editing of values from multiple tables in a query with a join.
> If you convert all these things to views and stored procedures, you have
> to
> figure out how to do things like bind forms to parameterized stored
> procedures, and implement extra code and forms for editing that were never
> needed before, etc. It's a huge, complex, error prone exercise that
> forces
> you to add complexity and remove convinience in the UI, all for basically
> no
> return on investment.
> Now, you're going to say that sometimes Access does not do a good job of
> producing the server-side SQL. Yup, that's true, so in -those- cases,
> using a
> view and/or a stored procedure can be very beneficial. You're also going
> to
> say that if you have multiple systems accessing the same back-end, you
> want to
> make the database interactions consistent and centralize business logic.
> I
> say, yes, but again, do that incrementally as needed for specific cases.
> If
> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
> great
> language for it anyway, and you probably should think about a 3-tier
> system
> and dump the Access UI altogether.
>
> Here's a case where I do recommend doing it the "hard way. I've never
> seen
> the upsizing wizard do a particularly good job. For each table, you
> should be
> deciding what should be your clustered index, whether you need a TIMESTAMP
> field, checking for NULL in Boolean fields, etc.
>
> An excellent suggestion - I agree.
>
|||Yup - I'd say my knee was a bit quick on the jerk there <g>.
On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi Steve
>I don't disagree with anything you said, but you seemed to have missed the
>caveat in the first sentence, i.e. remove access all together and you don't
>really have any choice.
>John
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com.. .
>
|||Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Thanks,
Teri Welch
"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1103273150.975672.240020@.z14g2000cwz.googlegr oups.com...
> There are a number of tools you can use to upsize the database, but in
> my experience, I would tend to do this manually to ensure everything in
> the structure is correct.
> Your main issue will be with the queries as the syntax can be slightly
> different to SQL. It's only subtle, but you would need to address this.
> Personally, I would manually create the tables (inc indexes etc...)
> then move the data using the import wizard and do any manipulation you
> need to get this correctly into the tables. Then, I'd address each
> query step by step in VB.
> There shouldn't be too many issues (as a general statement). It may be
> worthwhile looking at using views and stored procedures as these may
> give you additional benefits.
> What you will find is that once done, it's probably quicker and much
> less likely to have issues with record locking as SQL handles this much
> better.
> With your reports, I would imagine you will leave these in Access, set
> up an ODBC link to your SQL tables in access and call the reports
> through VB as you will currently do. All you need to do is swap the
> links over and it should be straight forward. You may do this and then
> think about leaving the links in for the queries, but I would advise
> not to on experience and for simplicity.
> I'm sure if there are any specific problems, a number of people on this
> NG will help, if not, try...
>
http://groups.google.com/groups?hl=e...ases.ms-access
> Ryan
>
> Teri Welch wrote:
> database. All
> queries/reports
> right
> feel
> we're
> right
>
|||Hi Teri
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:s1hyd.8482$jn.4225@.lakeread06...
> Thanks for the replies from everyone.
> 1. How can we convert MS-Access import specs to SQL Server. We import many
> data files from outside sources -- in both ASCII and Excel formats -- and
> make heavy use of import specs within VB6 programs by invoking the
> TransferText and TransferSpreadsheet functions.
You can use DTS to import from many different locations.
> 2. Regarding reports, must we leave them in Access? We are inclined to
> remove Access completely from the picture. Maybe that is too drastic and
> you
> would advocate otherwise. But we question the performance of using Access
> reports and the necessity to have Access open everytime a report is
> invoked
> from within VB6. Plus, we have to keep Access installed. How can we create
> the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements.
> Thanks,
> Teri Welch
>
John
> "Ryan" <ryanofford@.hotmail.com> wrote in message
> news:1103273150.975672.240020@.z14g2000cwz.googlegr oups.com...
> http://groups.google.com/groups?hl=e...ases.ms-access
>
Access migration to SQL Server
We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.
Teri WelchHi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
John
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:xLuwd.5516$jn.1131@.lakeread06...
> Hello,
> We maintain a VB6 front-end application using an Access 2000 database. All
> code and forms are in VB6. The program also uses several queries/reports
> defined in Access. For corporate reasons we must move to SQL Server right
> away. Based on our configuration, we're hoping someone can give us a feel
> for what's involved here. Since we're only using Access for the data we're
> not sure what an "upsizing" would entail. Can someone point us in the
> right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch
>|||There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.
Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.
What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.
With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
Ryan
Teri Welch wrote:
> Hello,
> We maintain a VB6 front-end application using an Access 2000
database. All
> code and forms are in VB6. The program also uses several
queries/reports
> defined in Access. For corporate reasons we must move to SQL Server
right
> away. Based on our configuration, we're hoping someone can give us a
feel
> for what's involved here. Since we're only using Access for the data
we're
> not sure what an "upsizing" would entail. Can someone point us in the
right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch|||See if this is useful............
"Moving Your Access 2002 Database to SQL Server"
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql2k/html/sqlbackend.asp
Thanks
GYK|||On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi
>If you are going to remove access completely your access queries will need
>to be re-written as stored procedures or views and the reports rewritten in
>(say) VB. Alternatively you can use an Access Data Project where most of the
>code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
>The access upgrade wizard will migrate most things, although it can run into
>problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
>I suggest that you create a test system and copy your database and
>application onto it. Then you try the upgrade, and you will get a feel for
>what is involved and how much can be easily migrated.
An excellent suggestion - I agree.|||Hi Steve
I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.
John
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>>Hi
>>If you are going to remove access completely your access queries will need
>>to be re-written as stored procedures or views and the reports rewritten
>>in
>>(say) VB. Alternatively you can use an Access Data Project where most of
>>the
>>code can remain the same.
> I have to say it - bah! There - I said it.
> Every single solitary time I've seen someone post on Usenet asking about
> upsizing an Access database to SQL Server, the first response says
> something
> about having to convert all your queries to views and stored procedures.
> Frankly, that's not at all necessary, and a serious drain on time and
> money.
> Access/DAO mostly does a fine job of translating queries of linked tables
> into
> reasonable server-side SQL for execution, and it creates prepared
> statements
> for parameter queries, so SQL Server won't have to keep recompiling each
> query
> each time it sees it. Furthermore, Access is quite happy doing things
> like
> using a named query parameter to look up a value from a form control and
> allowing editing of values from multiple tables in a query with a join.
> If you convert all these things to views and stored procedures, you have
> to
> figure out how to do things like bind forms to parameterized stored
> procedures, and implement extra code and forms for editing that were never
> needed before, etc. It's a huge, complex, error prone exercise that
> forces
> you to add complexity and remove convinience in the UI, all for basically
> no
> return on investment.
> Now, you're going to say that sometimes Access does not do a good job of
> producing the server-side SQL. Yup, that's true, so in -those- cases,
> using a
> view and/or a stored procedure can be very beneficial. You're also going
> to
> say that if you have multiple systems accessing the same back-end, you
> want to
> make the database interactions consistent and centralize business logic.
> I
> say, yes, but again, do that incrementally as needed for specific cases.
> If
> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
> great
> language for it anyway, and you probably should think about a 3-tier
> system
> and dump the Access UI altogether.
>>The access upgrade wizard will migrate most things, although it can run
>>into
>>problems if you are using something that is more complex.
> Here's a case where I do recommend doing it the "hard way. I've never
> seen
> the upsizing wizard do a particularly good job. For each table, you
> should be
> deciding what should be your clustered index, whether you need a TIMESTAMP
> field, checking for NULL in Boolean fields, etc.
>>I suggest that you create a test system and copy your database and
>>application onto it. Then you try the upgrade, and you will get a feel for
>>what is involved and how much can be easily migrated.
> An excellent suggestion - I agree.
>|||Yup - I'd say my knee was a bit quick on the jerk there <g>.
On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi Steve
>I don't disagree with anything you said, but you seemed to have missed the
>caveat in the first sentence, i.e. remove access all together and you don't
>really have any choice.
>John
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
>> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
>> <jbellnewsposts@.hotmail.com>
>> wrote:
>>Hi
>>If you are going to remove access completely your access queries will need
>>to be re-written as stored procedures or views and the reports rewritten
>>in
>>(say) VB. Alternatively you can use an Access Data Project where most of
>>the
>>code can remain the same.
>> I have to say it - bah! There - I said it.
>> Every single solitary time I've seen someone post on Usenet asking about
>> upsizing an Access database to SQL Server, the first response says
>> something
>> about having to convert all your queries to views and stored procedures.
>> Frankly, that's not at all necessary, and a serious drain on time and
>> money.
>> Access/DAO mostly does a fine job of translating queries of linked tables
>> into
>> reasonable server-side SQL for execution, and it creates prepared
>> statements
>> for parameter queries, so SQL Server won't have to keep recompiling each
>> query
>> each time it sees it. Furthermore, Access is quite happy doing things
>> like
>> using a named query parameter to look up a value from a form control and
>> allowing editing of values from multiple tables in a query with a join.
>> If you convert all these things to views and stored procedures, you have
>> to
>> figure out how to do things like bind forms to parameterized stored
>> procedures, and implement extra code and forms for editing that were never
>> needed before, etc. It's a huge, complex, error prone exercise that
>> forces
>> you to add complexity and remove convinience in the UI, all for basically
>> no
>> return on investment.
>> Now, you're going to say that sometimes Access does not do a good job of
>> producing the server-side SQL. Yup, that's true, so in -those- cases,
>> using a
>> view and/or a stored procedure can be very beneficial. You're also going
>> to
>> say that if you have multiple systems accessing the same back-end, you
>> want to
>> make the database interactions consistent and centralize business logic.
>> I
>> say, yes, but again, do that incrementally as needed for specific cases.
>> If
>> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
>> great
>> language for it anyway, and you probably should think about a 3-tier
>> system
>> and dump the Access UI altogether.
>>The access upgrade wizard will migrate most things, although it can run
>>into
>>problems if you are using something that is more complex.
>> Here's a case where I do recommend doing it the "hard way. I've never
>> seen
>> the upsizing wizard do a particularly good job. For each table, you
>> should be
>> deciding what should be your clustered index, whether you need a TIMESTAMP
>> field, checking for NULL in Boolean fields, etc.
>>I suggest that you create a test system and copy your database and
>>application onto it. Then you try the upgrade, and you will get a feel for
>>what is involved and how much can be easily migrated.
>> An excellent suggestion - I agree.
>|||Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Thanks,
Teri Welch
"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1103273150.975672.240020@.z14g2000cwz.googlegroups.com...
> There are a number of tools you can use to upsize the database, but in
> my experience, I would tend to do this manually to ensure everything in
> the structure is correct.
> Your main issue will be with the queries as the syntax can be slightly
> different to SQL. It's only subtle, but you would need to address this.
> Personally, I would manually create the tables (inc indexes etc...)
> then move the data using the import wizard and do any manipulation you
> need to get this correctly into the tables. Then, I'd address each
> query step by step in VB.
> There shouldn't be too many issues (as a general statement). It may be
> worthwhile looking at using views and stored procedures as these may
> give you additional benefits.
> What you will find is that once done, it's probably quicker and much
> less likely to have issues with record locking as SQL handles this much
> better.
> With your reports, I would imagine you will leave these in Access, set
> up an ODBC link to your SQL tables in access and call the reports
> through VB as you will currently do. All you need to do is swap the
> links over and it should be straight forward. You may do this and then
> think about leaving the links in for the queries, but I would advise
> not to on experience and for simplicity.
> I'm sure if there are any specific problems, a number of people on this
> NG will help, if not, try...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
> Ryan
>
> Teri Welch wrote:
> > Hello,
> >
> > We maintain a VB6 front-end application using an Access 2000
> database. All
> > code and forms are in VB6. The program also uses several
> queries/reports
> > defined in Access. For corporate reasons we must move to SQL Server
> right
> > away. Based on our configuration, we're hoping someone can give us a
> feel
> > for what's involved here. Since we're only using Access for the data
> we're
> > not sure what an "upsizing" would entail. Can someone point us in the
> right
> > direction, tell us the main gotchas, etc. Thanks a lot.
> >
> > Teri Welch
>|||Hi Teri
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:s1hyd.8482$jn.4225@.lakeread06...
> Thanks for the replies from everyone.
> 1. How can we convert MS-Access import specs to SQL Server. We import many
> data files from outside sources -- in both ASCII and Excel formats -- and
> make heavy use of import specs within VB6 programs by invoking the
> TransferText and TransferSpreadsheet functions.
You can use DTS to import from many different locations.
> 2. Regarding reports, must we leave them in Access? We are inclined to
> remove Access completely from the picture. Maybe that is too drastic and
> you
> would advocate otherwise. But we question the performance of using Access
> reports and the necessity to have Access open everytime a report is
> invoked
> from within VB6. Plus, we have to keep Access installed. How can we create
> the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements.
> Thanks,
> Teri Welch
>
John
> "Ryan" <ryanofford@.hotmail.com> wrote in message
> news:1103273150.975672.240020@.z14g2000cwz.googlegroups.com...
>> There are a number of tools you can use to upsize the database, but in
>> my experience, I would tend to do this manually to ensure everything in
>> the structure is correct.
>> Your main issue will be with the queries as the syntax can be slightly
>> different to SQL. It's only subtle, but you would need to address this.
>> Personally, I would manually create the tables (inc indexes etc...)
>> then move the data using the import wizard and do any manipulation you
>> need to get this correctly into the tables. Then, I'd address each
>> query step by step in VB.
>> There shouldn't be too many issues (as a general statement). It may be
>> worthwhile looking at using views and stored procedures as these may
>> give you additional benefits.
>> What you will find is that once done, it's probably quicker and much
>> less likely to have issues with record locking as SQL handles this much
>> better.
>> With your reports, I would imagine you will leave these in Access, set
>> up an ODBC link to your SQL tables in access and call the reports
>> through VB as you will currently do. All you need to do is swap the
>> links over and it should be straight forward. You may do this and then
>> think about leaving the links in for the queries, but I would advise
>> not to on experience and for simplicity.
>> I'm sure if there are any specific problems, a number of people on this
>> NG will help, if not, try...
>>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&group=comp.databases.ms-access
>> Ryan
>>
>> Teri Welch wrote:
>> > Hello,
>> >
>> > We maintain a VB6 front-end application using an Access 2000
>> database. All
>> > code and forms are in VB6. The program also uses several
>> queries/reports
>> > defined in Access. For corporate reasons we must move to SQL Server
>> right
>> > away. Based on our configuration, we're hoping someone can give us a
>> feel
>> > for what's involved here. Since we're only using Access for the data
>> we're
>> > not sure what an "upsizing" would entail. Can someone point us in the
>> right
>> > direction, tell us the main gotchas, etc. Thanks a lot.
>> >
>> > Teri Welch
>
Access migration to SQL Server
We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.
Teri WelchHi
If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.
John
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:xLuwd.5516$jn.1131@.lakeread06...
> Hello,
> We maintain a VB6 front-end application using an Access 2000 database. All
> code and forms are in VB6. The program also uses several queries/reports
> defined in Access. For corporate reasons we must move to SQL Server right
> away. Based on our configuration, we're hoping someone can give us a feel
> for what's involved here. Since we're only using Access for the data we're
> not sure what an "upsizing" would entail. Can someone point us in the
> right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch|||There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.
Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.
Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.
There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.
What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.
With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.
I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...
http://groups.google.com/groups?hl=...bases.ms-access
Ryan
Teri Welch wrote:
> Hello,
> We maintain a VB6 front-end application using an Access 2000
database. All
> code and forms are in VB6. The program also uses several
queries/reports
> defined in Access. For corporate reasons we must move to SQL Server
right
> away. Based on our configuration, we're hoping someone can give us a
feel
> for what's involved here. Since we're only using Access for the data
we're
> not sure what an "upsizing" would entail. Can someone point us in the
right
> direction, tell us the main gotchas, etc. Thanks a lot.
> Teri Welch|||On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi
>If you are going to remove access completely your access queries will need
>to be re-written as stored procedures or views and the reports rewritten in
>(say) VB. Alternatively you can use an Access Data Project where most of the
>code can remain the same.
I have to say it - bah! There - I said it.
Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.
Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.
If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.
Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
>The access upgrade wizard will migrate most things, although it can run into
>problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
>I suggest that you create a test system and copy your database and
>application onto it. Then you try the upgrade, and you will get a feel for
>what is involved and how much can be easily migrated.
An excellent suggestion - I agree.|||Hi Steve
I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.
John
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com>
> wrote:
>>Hi
>>
>>If you are going to remove access completely your access queries will need
>>to be re-written as stored procedures or views and the reports rewritten
>>in
>>(say) VB. Alternatively you can use an Access Data Project where most of
>>the
>>code can remain the same.
> I have to say it - bah! There - I said it.
> Every single solitary time I've seen someone post on Usenet asking about
> upsizing an Access database to SQL Server, the first response says
> something
> about having to convert all your queries to views and stored procedures.
> Frankly, that's not at all necessary, and a serious drain on time and
> money.
> Access/DAO mostly does a fine job of translating queries of linked tables
> into
> reasonable server-side SQL for execution, and it creates prepared
> statements
> for parameter queries, so SQL Server won't have to keep recompiling each
> query
> each time it sees it. Furthermore, Access is quite happy doing things
> like
> using a named query parameter to look up a value from a form control and
> allowing editing of values from multiple tables in a query with a join.
> If you convert all these things to views and stored procedures, you have
> to
> figure out how to do things like bind forms to parameterized stored
> procedures, and implement extra code and forms for editing that were never
> needed before, etc. It's a huge, complex, error prone exercise that
> forces
> you to add complexity and remove convinience in the UI, all for basically
> no
> return on investment.
> Now, you're going to say that sometimes Access does not do a good job of
> producing the server-side SQL. Yup, that's true, so in -those- cases,
> using a
> view and/or a stored procedure can be very beneficial. You're also going
> to
> say that if you have multiple systems accessing the same back-end, you
> want to
> make the database interactions consistent and centralize business logic.
> I
> say, yes, but again, do that incrementally as needed for specific cases.
> If
> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
> great
> language for it anyway, and you probably should think about a 3-tier
> system
> and dump the Access UI altogether.
>>The access upgrade wizard will migrate most things, although it can run
>>into
>>problems if you are using something that is more complex.
> Here's a case where I do recommend doing it the "hard way. I've never
> seen
> the upsizing wizard do a particularly good job. For each table, you
> should be
> deciding what should be your clustered index, whether you need a TIMESTAMP
> field, checking for NULL in Boolean fields, etc.
>>I suggest that you create a test system and copy your database and
>>application onto it. Then you try the upgrade, and you will get a feel for
>>what is involved and how much can be easily migrated.
> An excellent suggestion - I agree.|||Yup - I'd say my knee was a bit quick on the jerk there <g>.
On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jbellnewsposts@.hotmail.com>
wrote:
>Hi Steve
>I don't disagree with anything you said, but you seemed to have missed the
>caveat in the first sentence, i.e. remove access all together and you don't
>really have any choice.
>John
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kd26s0pl1p1s0dr38ah8gftbqu762cvles@.4ax.com...
>>
>> On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
>> <jbellnewsposts@.hotmail.com>
>> wrote:
>>
>>>Hi
>>>
>>>If you are going to remove access completely your access queries will need
>>>to be re-written as stored procedures or views and the reports rewritten
>>>in
>>>(say) VB. Alternatively you can use an Access Data Project where most of
>>>the
>>>code can remain the same.
>>
>> I have to say it - bah! There - I said it.
>>
>> Every single solitary time I've seen someone post on Usenet asking about
>> upsizing an Access database to SQL Server, the first response says
>> something
>> about having to convert all your queries to views and stored procedures.
>> Frankly, that's not at all necessary, and a serious drain on time and
>> money.
>>
>> Access/DAO mostly does a fine job of translating queries of linked tables
>> into
>> reasonable server-side SQL for execution, and it creates prepared
>> statements
>> for parameter queries, so SQL Server won't have to keep recompiling each
>> query
>> each time it sees it. Furthermore, Access is quite happy doing things
>> like
>> using a named query parameter to look up a value from a form control and
>> allowing editing of values from multiple tables in a query with a join.
>>
>> If you convert all these things to views and stored procedures, you have
>> to
>> figure out how to do things like bind forms to parameterized stored
>> procedures, and implement extra code and forms for editing that were never
>> needed before, etc. It's a huge, complex, error prone exercise that
>> forces
>> you to add complexity and remove convinience in the UI, all for basically
>> no
>> return on investment.
>>
>> Now, you're going to say that sometimes Access does not do a good job of
>> producing the server-side SQL. Yup, that's true, so in -those- cases,
>> using a
>> view and/or a stored procedure can be very beneficial. You're also going
>> to
>> say that if you have multiple systems accessing the same back-end, you
>> want to
>> make the database interactions consistent and centralize business logic.
>> I
>> say, yes, but again, do that incrementally as needed for specific cases.
>> If
>> you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
>> great
>> language for it anyway, and you probably should think about a 3-tier
>> system
>> and dump the Access UI altogether.
>>
>>>The access upgrade wizard will migrate most things, although it can run
>>>into
>>>problems if you are using something that is more complex.
>>
>> Here's a case where I do recommend doing it the "hard way. I've never
>> seen
>> the upsizing wizard do a particularly good job. For each table, you
>> should be
>> deciding what should be your clustered index, whether you need a TIMESTAMP
>> field, checking for NULL in Boolean fields, etc.
>>
>>>I suggest that you create a test system and copy your database and
>>>application onto it. Then you try the upgrade, and you will get a feel for
>>>what is involved and how much can be easily migrated.
>>
>> An excellent suggestion - I agree.
>|||Thanks for the replies from everyone.
1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Thanks,
Teri Welch
"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1103273150.975672.240020@.z14g2000cwz.googlegr oups.com...
> There are a number of tools you can use to upsize the database, but in
> my experience, I would tend to do this manually to ensure everything in
> the structure is correct.
> Your main issue will be with the queries as the syntax can be slightly
> different to SQL. It's only subtle, but you would need to address this.
> Personally, I would manually create the tables (inc indexes etc...)
> then move the data using the import wizard and do any manipulation you
> need to get this correctly into the tables. Then, I'd address each
> query step by step in VB.
> There shouldn't be too many issues (as a general statement). It may be
> worthwhile looking at using views and stored procedures as these may
> give you additional benefits.
> What you will find is that once done, it's probably quicker and much
> less likely to have issues with record locking as SQL handles this much
> better.
> With your reports, I would imagine you will leave these in Access, set
> up an ODBC link to your SQL tables in access and call the reports
> through VB as you will currently do. All you need to do is swap the
> links over and it should be straight forward. You may do this and then
> think about leaving the links in for the queries, but I would advise
> not to on experience and for simplicity.
> I'm sure if there are any specific problems, a number of people on this
> NG will help, if not, try...
>
http://groups.google.com/groups?hl=...bases.ms-access
> Ryan
>
> Teri Welch wrote:
> > Hello,
> > We maintain a VB6 front-end application using an Access 2000
> database. All
> > code and forms are in VB6. The program also uses several
> queries/reports
> > defined in Access. For corporate reasons we must move to SQL Server
> right
> > away. Based on our configuration, we're hoping someone can give us a
> feel
> > for what's involved here. Since we're only using Access for the data
> we're
> > not sure what an "upsizing" would entail. Can someone point us in the
> right
> > direction, tell us the main gotchas, etc. Thanks a lot.
> > Teri Welch|||Hi Teri
"Teri Welch" <trwlch@.yahoo.com> wrote in message
news:s1hyd.8482$jn.4225@.lakeread06...
> Thanks for the replies from everyone.
> 1. How can we convert MS-Access import specs to SQL Server. We import many
> data files from outside sources -- in both ASCII and Excel formats -- and
> make heavy use of import specs within VB6 programs by invoking the
> TransferText and TransferSpreadsheet functions.
You can use DTS to import from many different locations.
> 2. Regarding reports, must we leave them in Access? We are inclined to
> remove Access completely from the picture. Maybe that is too drastic and
> you
> would advocate otherwise. But we question the performance of using Access
> reports and the necessity to have Access open everytime a report is
> invoked
> from within VB6. Plus, we have to keep Access installed. How can we create
> the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements.
> Thanks,
> Teri Welch
John
> "Ryan" <ryanofford@.hotmail.com> wrote in message
> news:1103273150.975672.240020@.z14g2000cwz.googlegr oups.com...
>> There are a number of tools you can use to upsize the database, but in
>> my experience, I would tend to do this manually to ensure everything in
>> the structure is correct.
>>
>> Your main issue will be with the queries as the syntax can be slightly
>> different to SQL. It's only subtle, but you would need to address this.
>>
>> Personally, I would manually create the tables (inc indexes etc...)
>> then move the data using the import wizard and do any manipulation you
>> need to get this correctly into the tables. Then, I'd address each
>> query step by step in VB.
>>
>> There shouldn't be too many issues (as a general statement). It may be
>> worthwhile looking at using views and stored procedures as these may
>> give you additional benefits.
>>
>> What you will find is that once done, it's probably quicker and much
>> less likely to have issues with record locking as SQL handles this much
>> better.
>>
>> With your reports, I would imagine you will leave these in Access, set
>> up an ODBC link to your SQL tables in access and call the reports
>> through VB as you will currently do. All you need to do is swap the
>> links over and it should be straight forward. You may do this and then
>> think about leaving the links in for the queries, but I would advise
>> not to on experience and for simplicity.
>>
>> I'm sure if there are any specific problems, a number of people on this
>> NG will help, if not, try...
>>
>>
> http://groups.google.com/groups?hl=...bases.ms-access
>>
>> Ryan
>>
>>
>> Teri Welch wrote:
>> > Hello,
>>> > We maintain a VB6 front-end application using an Access 2000
>> database. All
>> > code and forms are in VB6. The program also uses several
>> queries/reports
>> > defined in Access. For corporate reasons we must move to SQL Server
>> right
>> > away. Based on our configuration, we're hoping someone can give us a
>> feel
>> > for what's involved here. Since we're only using Access for the data
>> we're
>> > not sure what an "upsizing" would entail. Can someone point us in the
>> right
>> > direction, tell us the main gotchas, etc. Thanks a lot.
>>> > Teri Welch
>>
Monday, February 13, 2012
Access front-end to SQL back-end
my old company (UPS) used the server to help expedite queries and
manage on-line reporting. So, now it's up to me to utilize and
understand the new server. I have been using MS Access as my front-
end for several ad-hoc databases, so I'd like to keep utilizing this
method. I want to test my first database using the SQL Server.
I've got everything set up. The IT department has installed the
server software on my computer. Within my Access database, I link in
tables from a database called "Progress" using a Merant driver.
Queries, forms, macros and modules are all pulling from these "linked"
tables. Do I need to link these tables into the SQL Server? How do I
transistion this database over to SQL? I guess I'm confused about the
relationship of how all of this should work.
Right now it looks like this:
PROGRESS .... linked...... MS Access ...... reporting/results
Where does SQL fit in?Hi,
What you need to do is migrate your database from whatever RDBMS you are
using to SQL Server. That means transferring the structure of your
database as well as the data. I don't know what RDBMS you are using so
I can't give you detailed instruction on how to do it. Essentially you
need to put your existing RDBMS out of service in favor of SQL Server.
So you can't link the exsiting "tables into SQL Server". You have to
transfer them completely.
A number tools exist for transferring data to SQL Server including bcp,
and DTS/SSIS.
Jonathan
mssbass wrote:
> I'm new to SQL Server 2000. I decided to purchase this server because
> my old company (UPS) used the server to help expedite queries and
> manage on-line reporting. So, now it's up to me to utilize and
> understand the new server. I have been using MS Access as my front-
> end for several ad-hoc databases, so I'd like to keep utilizing this
> method. I want to test my first database using the SQL Server.
>
> I've got everything set up. The IT department has installed the
> server software on my computer. Within my Access database, I link in
> tables from a database called "Progress" using a Merant driver.
> Queries, forms, macros and modules are all pulling from these "linked"
> tables. Do I need to link these tables into the SQL Server? How do I
> transistion this database over to SQL? I guess I'm confused about the
> relationship of how all of this should work.
>
> Right now it looks like this:
>
> PROGRESS .... linked...... MS Access ...... reporting/results
>
> Where does SQL fit in?
>
Access front-end - concurrency
Is Access smart enough to do the job ?
Thanks.Data integrity should be handled by SQL Server, using constraints, foreign keys, triggers, etc.
Concurrency should not be an issue as long as you keep the number of simultaneous users low. Access forms operate by downloading an entire recordset and allowing the user to step through them. This is not the optimal implementation for an Enterprise Application. You can get around this by writing your forms to retrieve one record at a time, but then you sacrifice these simple coding that is one of the big benefits of using Access.
Access Front-End
If we were to move the SQL database onto a new server then would it just be
a case of editing the ODBC settings on each client to point to the new SQL
server?
Yes as long as the SQL Server end of it is correctly moved
(login, users, etc)
-Sue
On Thu, 21 Jul 2005 08:27:04 -0700, HSP
<HSP@.discussions.microsoft.com> wrote:
>We have a system which uses an Access front end linked to a SQL 2000 database
>If we were to move the SQL database onto a new server then would it just be
>a case of editing the ODBC settings on each client to point to the new SQL
>server?
Access Front-End
e
If we were to move the SQL database onto a new server then would it just be
a case of editing the ODBC settings on each client to point to the new SQL
server?Yes as long as the SQL Server end of it is correctly moved
(login, users, etc)
-Sue
On Thu, 21 Jul 2005 08:27:04 -0700, HSP
<HSP@.discussions.microsoft.com> wrote:
>We have a system which uses an Access front end linked to a SQL 2000 databa
se
>If we were to move the SQL database onto a new server then would it just be
>a case of editing the ODBC settings on each client to point to the new SQL
>server?