Solved Importing From Excel To SQL Server

[Ashfaque]

Hi,

I tried importing data from Excel sheet to local tbl first which was success. There are 107 fields in Excel Sheet and I have created same fields in local tbl and was importing the data successfully.

Now I moved my local tbl to SQL server and trying to follow the same process of importing data thru code but it doesn't work. I tried with new additional field to server tbl (new field name ID, Int, Not Null) which might be require for the tbl to add record but still not importing data to table.

Second thing is; I am not able to select incremental Yes/No for the new field ID.

Can someone guide me please....

 

[arnelgp]

you create the Table first in SQL server using SQL Management studio.
you can define ID as Int.
on it's Property choose ID as Identity Column.

from ms create a linked table and append the records (not including the autonumber).

 

[Ashfaque]

you create the Table first in SQL server using SQL Management studio.
you can define ID as Int.
on it's Property choose ID as Identity Column.

from ms create a linked table and append the records (not including the autonumber).

You mean instead of using imported tbl structure using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_PreInterDetails" method, I will have to create new tble?

In Excel sheet 107 fields are there so I kept I kept same 107 field in Access.

And then how about new field ID which I set in SQL tbl? How it will be fill up with incremental numbers where as in Excel sheet there is no such kind of field.

 

[arnelgp]

if you already Imported the excel to new table, no need.
you also need to Create a Table in MSSQL Server with
structure (somewhat) similar to your Imported table.

then link the table in MSSQL server to your MSAccess.
then create an Insert query to insert the records
from imported excel to linked table.

 

[Ashfaque]

if you already Imported the excel to new table, no need.
you also need to Create a Table in MSSQL Server with
structure (somewhat) similar to your Imported table.

then link the table in MSSQL server to your MSAccess.
then create an Insert query to insert the records
from imported excel to linked table.

                 If IsNull(Len(Dir("D:\Ashfaque\ORS.xlsx"))) = False Then
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_PreInterDetails", _
                "D:\Ashfaque\ORS.xlsx", True
                        MsgBox "All Job Applications Imported..."
                 Else
                        MsgBox "No data file to import"
                Exit Sub
                End If

The attached code I was using to import data when the tbl was locally placed. As you said I moved this tbl to server and re-linked to Access and and using the same trick of code, it wont transfer the data.

If I need to use INSERT INTO method then we need to use Do While in it because each time excel sheet has indefinite number of records...say sometime 2000 rec sometime 1800 etc...

 

[Isaac]

You can also look into using t-sql bulk insert, if your SQL server permissions on your account allow for it. I've recently used this and been very happy with the speed with which it loads.

You can use a pass-through query in access to execute a stored procedure which you create on SQL server in order to perform the insert.

 

[Ashfaque]

Sir,

This is first time I used BE as server so I am not much expert in t-sql and even I dont have idea for what it stands for and how to use it. I know little about Pass thru in Access but never used that even.

 

[arnelgp]

As you said I moved this tbl to server and re-linked to Access and and using the same trick of code, it wont transfer the data.

i never said Move your table to SQL server.
what i said is you Create New table in SQL server (using SQL Management Studio) and build similar column.
next, from ms access, create a Linked Table from the table you created in SMSSQL.
next, insert the record to this linked table using the imported table you have.

 

[Ashfaque]

i never said Move your table to SQL server.
what i said is you Create New table in SQL server (using SQL Management Studio) and build similar column.
next, from ms access, create a Linked Table from the table you created in SMSSQL.
next, insert the record to this linked table using the imported table you have.

This is I did already. Created tbl in server and re-linked in Access with all same columns that I am importing from excel sheet. The Excel sheet has 107 columns and my tbl also have 107 columns with similar names in it.

But there is no PK set in server tbl. Probably PK is require to add records in server tbl. I am right?

 

[arnelgp]

import the Excel file First to ms access (as Local Table).
then Insert the record from this Local table to your Linked table.

you nee to create an Identity column in SQL server.

 

[Galaxiom]

import the Excel file First to ms access (as Local Table).
then Insert the record from this Local table to your Linked table.

This is incredibly slow though it probably won't matter with small numbers of records.

 

[arnelgp]

This is incredibly slow though

Instead of criticizing, show your Best googled approached old man.

 

[Ashfaque]

import the Excel file First to ms access (as Local Table).
then Insert the record from this Local table to your Linked table.

you nee to create an Identity column in SQL server.

OK I will try this way....

 

[Ashfaque]

import the Excel file First to ms access (as Local Table).
then Insert the record from this Local table to your Linked table.

you nee to create an Identity column in SQL server.

I added one additional field as ID with NotNull, Datatype Int and default value as 0 on server. Now when I linked the tbl in Access, it show updateable which previously it was not.

Now how when the data will transfer from other tble or direct from Excel sheet, how an I set ID value from 1 to onwards ?

 

[arnelgp]

How many times will i tell you.
Make the Id field in mssql, identity column.
So it will auto populate.

 

[Ashfaque]

How many times will i tell you.
Make the Id field in mssql, identity column.
So it will auto populate.

Arnel, I did it already. I created Identity column.

I think I need to modify the codes. As a sample, I will take only 2-3 fields to transfer from excel to the server tble.

 

[Ashfaque]

Now, I have transferred data from Excel sheet to local table and to transfer data local tbl to server tbl, I have written following code.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim fld As DAO.Field

Set rst = CurrentDb.OpenRecordset("Select * from T_PreInterDetails_Pre", dbOpenDynaset, dbSeeChanges)
rst.MoveFirst
X = 0
Do While Not rst.EOF
    rst.Edit
    
 Set rst2 = CurrentDb.OpenRecordset("Select *  from T_PreInterDetails", dbOpenDynaset, dbSeeChanges)
rst2.AddNew

    For Each fld In rst2.Fields
     If fld.name <> "ID" Then _
     rst2(fld.name) = rst(fld.name)
     X = X + 1
    Next fld
     rst.MoveNext
 
    rst2.Update
       Loop
    MsgBox ("Data Updated On Server Table")
    
rst2.Close
rst.Close
Set db = Nothing

And this worked well....

 

[arnelgp]

you can also use SQL Insert statement:

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim sInsertSQL As String
Dim sFields As String
Set db = CurrentDb
Set td = db.TableDefs("T_PreInterDetails_Pre")
For Each fld In td.Fields
    If fld.Attributes And dbAutoIncrField Then
        'do not include autonumber field
    Else
        sFields = sFields & "[" & fld.Name & "],"
    End If
Next
Set fld = Nothing
Set td = Nothing
sFields = Left$(sFields, Len(sFields) - 1)
sInsertSQL = "Insert Into [T_PreInterDetails] (" & sFields & ") " & _
    "SELECT " & sFields & " FROM [T_PreInterDetails_Pre];"
db.Execute sInsertSQL, dbSeeChanges
Set db = Nothing

 

[Ashfaque]

you can also use SQL Insert statement:

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim sInsertSQL As String
Dim sFields As String
Set db = CurrentDb
Set td = db.TableDefs("T_PreInterDetails_Pre")
For Each fld In td.Fields
    If fld.Attributes And dbAutoIncrField Then
        'do not include autonumber field
    Else
        sFields = sFields & "[" & fld.Name & "],"
    End If
Next
Set fld = Nothing
Set td = Nothing
sFields = Left$(sFields, Len(sFields) - 1)
sInsertSQL = "Insert Into [T_PreInterDetails] (" & sFields & ") " & _
    "SELECT " & sFields & " FROM [T_PreInterDetails_Pre];"
db.Execute sInsertSQL, dbSeeChanges
Set db = Nothing

Thanks Arnel,

My code is working but it is comparatively slower than your code.

You code works fast.

I appreciate your help.

Thanks,

 

[Minty]

The difference is @arnelgp code is using set-based update, your method is sometimes called RBAR .
You should where possible always try and use a set-based update or insert in your processes.