Results 1 to 21 of 21

Thread: Move from native .dbf to SQL Server Express

  1. #1
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Move from native .dbf to SQL Server Express

    I have been designing my forms and other things against the native .dbf files. My original intent was to offer 2 versions my app, one with the native .dbf backend and the other with SQL Server Express as the database. I have replicated the the native .dbf files in SQL Server. All that is left is for me to make copy of the current database and change the data connection in the copy to SQL side. What I have not found is the way to do that.

    Can someone point me to the path?

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    22,528

    Default Re: Move from native .dbf to SQL Server Express

    All that is left is for me to make copy of the current database and change the data connection in the copy to SQL side.
    Not quite. You need to establish passive or active link tables to the sql tables to support your layouts. Not all controls will perform as expected with these link tables.

  3. #3
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Thanks for the response, so let me start by saying ------- Uh - Oh!

    It looks like I have done some poor choices at the start. As they used to say, 'if it was easy anyone can do it'. It appears I may be a victim of my own bad planning and doesn't sound like the backend conversion is as easy as I had hoped. There is more work to do. Still I guess now was the time for me to find it out so I can set about the fixes.

    Thanks for the heads up on the control issues, I will be on the look out for them in my testing.

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    22,528

    Default Re: Move from native .dbf to SQL Server Express

    Might I suggest that you experiment with the Access mdb files in the C:\Program Files\a5V11\MDBFiles directory? Use the AlphaSports mdb and add some or all of the tables to the desktop Alphasports sample database. Then Copy To... the link tables from the native tables and examine the results.

    Ceartainly not an expert in this area but have done some poking around.

  5. #5
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Stan - Thanks for the responses. Here the progress I have made which I am sure is old ground to you.

    I copied the database to the my new folder for the SQL version. I followed the guidelines in the help file for doing this. I imported (added) my SQL Express 2012 database as active link tables. I should note that when I first opened database copy it was still wired to the native the dbf files in the original database. I then dropped the native .dbf tables from the control panel and was left with my active link tables showing. I then repaired each of the sets to use the those tables.

    I opened my first form and everything at first glance seem to be working.

    What I cannot figure out yet is how to generally rename the database. I am a bit nervous to simply change the name in Windows Explorer for fear of breaking something. There is nothing in the help file about changing names.

    I wish there was an easier way to do this, there are a number of steps to the way I went about it. But it appears to give me what I wanted - a Native .dbf and a SQL Server Express version of the application.

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    22,528

    Default Re: Move from native .dbf to SQL Server Express


  7. #7
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    I have renamed the database, It worked. I also found some instructions on doing this on the Wiki. It appears a couple of my forms have glitches. They show in the Control Panel but all the design such as controls etc are missing. Never easy I guess....

  8. #8
    Member
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    836

    Default Re: Move from native .dbf to SQL Server Express

    Beware I'm Still a newbie in alpha five,just my own opinion and little experience, Although alpha five offers many great features, there are certain areas wherein you have to deal with speed in your application, have tried what you are trying to achieve, passive or active link tables are not quite fast enough in some areas, especially when dealing with lookup with lots of records.

  9. #9
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Jetli

    I am not sure about a performance hit using active link tables in a desktop application. I hope it will not be huge since I would like have the two variations of the app. Anyway thanks for the reminder.

  10. #10
    Member
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    836

    Default Re: Move from native .dbf to SQL Server Express

    John, I could be wrong, please let me know if you are getting a good result in active and passive link tables.

  11. #11
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,640

    Default Re: Move from native .dbf to SQL Server Express

    From what other developers have told me, using "linked" tables in Alpha in a desktop app perform very poorly. They say you have to use xdialog to create the forms, and even then be quite experienced in doing so. I've heard you are better off starting from scratch and writing a web app if you want to use SQL. You can still run it on a LAN. Go to the web forum and ask around over there.
    Cole Custom Programming
    www.martinwcole.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  12. #12
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Martin - Thanks for the information between you and JetLi I am reallly discouraged. I do have the app in the native .dbf version but was really hoping to be able to connect to SQL Server. You would think with SQL Server, and ODBC and OLEDB connections to it, being around for so long A5 would be able to connect using those methods and maintain good performance. Increasingly I find myself looking back at Visual Studio and thinking maybe its a better tool for what I want to create. I can develop using the new LocalDb version of SQL Server and hold pretty reasonable performance. I can also migrate to other versions of SQL Server without hassle.

    I cannot tell you how much this bums me out...

  13. #13
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,640

    Default Re: Move from native .dbf to SQL Server Express

    John, it may be a reason for being frustrated and disappointed, but not a good reason for abandoning Alpha.
    Its just that you will need to go to school on using Alpha and SQL - i.e., the web version and/or the client server, which are pretty much the same as far as I know. Or becomming an xdialog jock. From what I've read, you can develop a web interface much quicker with Alpha than the other platforms you mentioned.
    Cole Custom Programming
    www.martinwcole.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  14. #14
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Martin - thanks for a bit of perspective. I will give XDialog more of a look. This could be my frustration creeping in again , but it does seem if I need to hand code and create my forms through XDialog, which I take is done primarily through code, then A5 has ceased being a RAD product. My target customers are desktop based and not so inclined to incur the added expense of the Web App Server. I also really have little interest in taking on the support of a number of app servers spread across the US.

  15. #15
    Member
    Real Name
    Jetson Lilibeth
    Join Date
    Dec 2011
    Posts
    836

    Default Re: Move from native .dbf to SQL Server Express

    John, when i decided to looak at alpha five as my database development tool, the one that encouraged me was its RAD, we can develop a simple fucntional database application even if we are still new to alpha compared to visual studio. Alpha is very good in its native database which is .dbf. The one that makes me hesitant is that its desktop side is in my own opinion displaying some unpredicatable behaviors more so that Im still new to it, lately, i am discovering errors which i don't know if it is my own error or some hidden bugs, I almost gave up using Alpha five because of this,many many many years alpha five existed but even the desktop part is not yet perfected, SOME users complain of SOME unpredicatable behaviors especially on the suppose to be very coll browse control but......, believe me, I almost gave up! The only thing that kept me using it is, the HELP that I am getting on this forum, If not for Stan Mathews and Tom Cone Jr. to name a few, I would have abandoned Alpha Five in a corner somewhere for dust to cover it, I have never found a support like this on the Internet,that you can have an answer the next 24 hours, I was thinking before to leave alpha five and continue visual studio 2010, sure we can download lots and lots of examples for C#, but sometimes your specific questions cannot be easily found. Visual Studio is very good indeed if you have lots of time learning it, Alpha five is okey if you don't have enough, Xdialog as they say is better in handling data entries BUT alpha is suppose to be a RAD tool, I hope xdialog will be easier to create, In my opinion, I would rather learn visual studio (C#) rather than learning Xdialog.

  16. #16
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,629

    Default Re: Move from native .dbf to SQL Server Express

    John,

    Just a quick question. What is your reasoning for using BOTH native .dbf and SQL? We have a very large application with a couple of hunderd users, and we are still using native .dbf files. We had used ver 5 for many years, and with version 10, we found that we could achieve the save performance we had with 5. I have thought about SQL as our tables are extremely large, but so far, I really can't justify the time and effort it would take to convert.

    Just my opinion,

    Tom

  17. #17
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Tom

    Since its not completed I will say I am attempting to build an off the shelf software product for small governments to help with their business process. My target is small local governments that cannot afford large enterprise scale systems. Some of these entities are one or two person shops and some may have several employees. What is pretty typical is these are Microsoft shops using Office and related software. Some will use MS SQL Server as a database. My simple goal, at least simple in my mind, was to offer two versions of the package. One the native .dbf and another that would use tie into their SQL Server system. Governments a strange beasts in not wanting to go to far from what they consider the mainstream. I will in the end have enough difficulty in marketing the A5 front-end let alone the backend system.

    Functionally I appreciate the native dbf system is robust enough to handle the work, so its more of a marketing ploy. There is somewhat of a difference between marketing an off-the-shelf product for customers than developing for an in-house system.

    So I reached a point where I wanted to see if I could split the current 'trunk' into two and what were the issues. I made the split and found a couple of things did not port over, I fixed those and now have two versions of the app at least as far as it has progressed. So it does work, however then this whole conversation came up about performance and that makes me nervous.

    I have looked at xDialog and have done some tests, but Wow! RAD its not, at least for me. If A5 cannot hold reasonable performance with an industry standard such as MS SQL Server then .... I have looked at some other products such as RealStudio (RealBasic) and I am currently using the demo version. but the jury is out.

    All things considered, I moved to A5 because it has so much going and what I consider its strong history of developer support. It has a terrific licensing system. I am reluctant at this point to move away from it but if I cannot connect to SQL Server and maintain performance then it may not be right tool for this project.

    Sorry for the lengthy explanation and proabaly more information than anyone needs, but I hope the backstory is useful to the conversation.

  18. #18
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    10,543

    Default Re: Move from native .dbf to SQL Server Express

    Quote Originally Posted by jkwrpc View Post
    I made the split and found a couple of things did not port over, I fixed those and now have two versions of the app at least as far as it has progressed. So it does work, however then this whole conversation came up about performance and that makes me nervous.
    John,

    If it works, where is the issue? I think the main complaint on desktop sql performance is concerning sets. Dump 20,000 dummy records into you sql tables and see how she runs.

    P.S. I agree, xdialog is definitely not "RAD" even if it is rad.

  19. #19
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,629

    Default Re: Move from native .dbf to SQL Server Express

    John,

    Interesting that you say governments are a strange beast. I work for a County Welfare agency.

    We have developed our system entirely with native .dbf files as there was no such thing as an interface to SQL or any other type of system back then (Alpha4 ver 3). We chose Alpha a long time ago mostly for price and ease of development. Our development efforts have strayed dramatically from simple, but we can still generate a requested report or form or whatever extremely rapidly.

    Our fiscal package uses some sort of sql system, but as it is propritary, we export our data to a csv file and it is imported into their software.

    I do understand your desire to have both, but wouldn't you need to customize heavily based on their sql tables anyway?

    Tom

  20. #20
    Member jkwrpc's Avatar
    Real Name
    John Willems
    Join Date
    Jul 2009
    Location
    Apple Valley, Minnesota
    Posts
    447

    Default Re: Move from native .dbf to SQL Server Express

    Tom

    I worked in the government well over 40 years including the federal, state, county and city levels. I continue to believe they are strange beasts when it comes to IT stuff. They want stuff to work, they like whiz-bang, but have limited funding. The departments within various levels are not consistent in their IT structures but they would like to be. The engage in contracts, have preferred vendors, engage in a constant change of centralizing to decentralizing then back again all in the name of efficiency. They want to control their own data centers and don't like the cloud, but then for some things such as email the cloud is okay. It goes on and on... Oh! and then wait for the new IT Director to show up and its all in flux again.

    Anyway back to my app, it was my intent to simply install or attach my SQL Server database to an entities existing SQL Server. I had no intent to tie my front end to their existing data. It is no longer an off the shelf application at that point but needs customization. Being a one man shop I could not support that kind of environment very well.

  21. #21
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,629

    Default Re: Move from native .dbf to SQL Server Express

    John,

    I know the "fluidity" of Government. In my former life, I worked for a mainframe systems company supporting their operating systems and marketing to the state, county, and local governments. Now I work for the county answering to county, state, and federal requirements. Makes for interesting days...

    Tom

Similar Threads

  1. Move from native .dbf backend to production SQL Server
    By jkwrpc in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 05-30-2012, 11:33 AM
  2. Mysql or Sql Server to Native Alpha DBF
    By JetLi in forum Alpha Five Version 11 - Desktop Applications
    Replies: 12
    Last Post: 03-21-2012, 05:39 PM
  3. Start with SQL or develop with native dbf's?
    By Steve Lipton in forum Database Design
    Replies: 1
    Last Post: 10-04-2010, 12:01 PM
  4. Move from SQL Express 2008 to SQL Standard, Can't INSERT new records now
    By Scholin in forum Application Server Version 10 - Web/Browser Applications
    Replies: 6
    Last Post: 02-13-2010, 04:26 PM
  5. Speed: native dbf compared to Oracle Express
    By rleunis in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 10-07-2008, 04:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

The Reviews Are In...

It just is revolutionary and reminds me of VB and how it changed the world.
quote Robert Scoble, Scobleizer

...Version 10 is a turning point on how developers will be writing applications for the web
quote Alan Ashendorf, Lets Talk Computers

Alpha Five version 10... this version is really a break-through for web developers.
quote The Wall Street Journal, Digital Network

Our Professional Services Division

Training and Mentoring - Alpha Software's Professional Services division is here to help. We offer mentoring and training services, for those who need guidance or advice building their own applications.

Development Services - Need someone to build your application? We'll get the job done right. We have an in-house team of Alpha Five developers and a network of carefully selected artists, designers and IT professionals ready to handle projects of any size. Read more .

submit a project request

Alpha Five Awards & Press
awards
Products Store Support Services About Cart Site Map Resources Home
© Copyright 2000-2011 Alpha Software, Inc. 70 Blanchard Road Burlington, MA 01803 781.229.4500
more