About Importing Access Tables into Sql

[drek01]

Till of now i was using accee built in import method to import access table from ( access database) into sql server.However now i have to do it from my front end( that is access, Code in VBA).
or ,


I'm guessing that i need to establish a connection to the selected database, and then query a specific table, and load the contents into sql server.

 

[lameid]

Usually the easiest way is to build your target table and append to a link of it.

 

[drek01]

actually is it just possible to add table in your sql databse.
i donot know how that works.
help me out here.

 

[drek01]

can you give me idea about linking table.

 

[drek01]

i have file browser which opens access database and loads the tables into list box. now what i want to do is to select the tables and able to import it into sql server.

how do i do this. i found sample code in VB to import excel spreadsheet into sql server, can you help me to understand this and to modify as per my need. i think its in VB, where as mine is VBA.

Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing

 

[lameid]

Maybe you want to use the upsizing wizard to put your access table in SQL server?

Do you NEED to do this programmatically? If so, why?
Why are you trying to do this in Access instead of VB.NET or using SQL Server tools to do it?

I don't have a good answer for you, I am hoping to be able to steer you in the right direction.

 

[danvlas]

If your Excel import works, how about changing the connection string of your connection?
Should work without any other change...

http://www.connectionstrings.com

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[drek01]

i donot know whether this works or not.

 

[drek01]

i used this code, and i am getting the error saying" The expression you entered is wrong datatype for one of the arguments."
i know i selected access database.

any idea. btw i am trying to import the selected table into sql server, which is my backend.
ANY HELP

Private Sub Listtables_AfterUpdate()
docmd.TransferDatabase acImport, "Microsoft Access", dbname, acTable, Me.ListTables, Me.ListTables
end Sub

 

[drek01]

AnyOne....plz

 

[drek01]

help me out here guys. it still gives me the expression you entered is wrong data type'
i even tried this with DSN

Private Sub ListTables_AfterUpdate()
docmd.TransferDatabase acLink, "Microsoft Access", dbname, acTable, Me.ListTables, Me.ListTables
docmd.TransferDatabase acExport, "ODBC Database", "ODBC;DSN=dsnname;LANGUAGE=us_english;DATABASE=databasename", acTable, Me.ListTables, Me.ListTables
End Sub

still gives me same error.let me know where i went wrong...

 

[drek01]

guys please help me out here.
wha ti am trying to do is import access table in my current back end database which is sql server, and my front end is access( using 2000).

after browsing accessdatabase and listing tables in listbpx, i wanted to import to my currentdatabase by selecting tabels from listbox.and i tried same code as well.without linking odbc databse and with linking none are working.
Private Sub Listtables_AfterUpdate()
docmd.TransferDatabase acImport, "Microsoft Access", dbname, acTable, Me.ListTables, Me.ListTables
end Sub



i donot know why its doing this...