VB DB MS SQL Server Question

Discussion in 'OT Technology' started by antiyou, Oct 4, 2006.

  1. antiyou

    antiyou OT Supporter

    Joined:
    Jul 13, 2005
    Messages:
    25,295
    Likes Received:
    0
    Location:
    in ur base
    Ok So I don't code in VB often this may seem a little awkward, but I have a project using VB and Access, I want to Convert it to use SQL, but the Access code is written in DAO and the SQL Commands are of the format ADO. I need to know what ms sql server file to reference, or is there another DB client I can use that is DAO.
     
  2. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    I looked at doing this a number of years ago and what I found was that although Access can act as a front end to an SQL DB, you lose a LOT of what makes Access great. Things may have changed but I don't think they have because of the differences in how things work. Sorry I don't have any suggestions for you.
     
  3. antiyou

    antiyou OT Supporter

    Joined:
    Jul 13, 2005
    Messages:
    25,295
    Likes Received:
    0
    Location:
    in ur base
    My reasoning for this is to get around a DB size limitation. The DB is crashing becuase of so many entries Access has a 2Gig limit I believe. Sql doesn't have the same problem I believe it either auto partions splitting the db and linking them, or has a larger limit. My other option is to partition split and link the DB myself before it reaches the limit. It looks like this may be the best solution.
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
  5. antiyou

    antiyou OT Supporter

    Joined:
    Jul 13, 2005
    Messages:
    25,295
    Likes Received:
    0
    Location:
    in ur base
    I want to port using minimal code changes. If I switch to ADO I will need to change all of my DB calls. So I want to use something that is DAO.
     
  6. antiyou

    antiyou OT Supporter

    Joined:
    Jul 13, 2005
    Messages:
    25,295
    Likes Received:
    0
    Location:
    in ur base
  7. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    All depends on how much code there is that deals with the tables. For example, reading and writing to a table is the same regardless of the data source.

    I would link the tables up using ODBC and try running it. Queries are the same, simple record searching, editing and deleting is the same. It's when you want to reindex or index a table thru vb that you would run into problems.

    The ODBC connector is under the admin tools in XP, connect to the MDF file and try it out.
     
  9. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    This is where object oriented programming really comes in handy... This would be such a simple conversion had there been some generic DataAccessLayer object that has simple methods like .connect() execute(), etc.
     
  10. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    ORM is my friend
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well there is - kind of. For example to specify a recordset, it's the same command regardless of the source data:

    set dbs = currentdb()
    set recset = dbs.OpenRecordset("Tablename", dyOpenDynaset)

    Tablename could be a native access table, query, linked ODBC table (all kinds of options here including SQL), excel sheet etc...

    Want to go to the front?

    recset.movefirst

    end?

    recset.movelast

    count the number of records?

    recset.count

    You get the idea. In other words, it all depends on how the code was written in the first place and what the database does.
     
  12. antiyou

    antiyou OT Supporter

    Joined:
    Jul 13, 2005
    Messages:
    25,295
    Likes Received:
    0
    Location:
    in ur base
    Well off topic google.com/codesearch is a really neat tool, I think it may have just been released yesterday.


    On-topic the person that wrote the code that I am now updating was not a software engineer he was a contractor with some "programing experience" I am told. Now I am left to clean up his mess.

    :ugh:
     

Share This Page