copy access db to sql server 2005 db-VBForums
Results 1 to 6 of 6

Thread: copy access db to sql server 2005 db

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    17

    copy access db to sql server 2005 db

    Hi,

    I want to create an app that will copy and paste the content from some mdb files automaticaly to an sql server 2005 db. These db should have the same format(same number of tables etc like the access file). What i should use in vb6 to do that?something like ado or adodb? First of all i should create a db to sql but how i could do that with the same form of the access files i will check the content and i then i will create them one by one is there any simplier way?

    thanks

  2. #2
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: copy access db to sql server 2005 db

    What version of SQL Server do you have? There is a function built into to Import database (tables and data are imported) from access to SQL Server. Have you used spaces in Table or Field Name in the access database? If so this will be an issue in the SQL Server database.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    17

    Re: copy access db to sql server 2005 db

    I have SQL Server 2005 and field names in the access database..

  4. #4
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: copy access db to sql server 2005 db

    SQL Server 2005 comes in Express version or Standard or Enterprise. Standard and Enterprise have the Imoprt functionality, I'm not sure about Express. As to field names I undertsand you have tables and fields in the Access database what I said do either of those things have any spaces in there name like this

    Table Name: Names of Contacts
    Fields Name: First Name

    If so that will cause issues.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2011
    Posts
    17

    Re: copy access db to sql server 2005 db

    GaryMazzone,
    issues like what?

    and if we will forget these issus how i will do that app?

  6. #6
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,266

    Re: copy access db to sql server 2005 db

    Spaces in names will cause SQL Server it complain about the SQL statements. Access allows them and even Access will complain about the spaces if you write SQL statements to run against the database with ADO or ADO.Net.

    The complaint will be invalid SQL Statement, the insert, update or delete will not run. There are ways around this of course but it is still just a work around and is not the correct way of doing things.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.