Solved Importing From Excel To SQL Server (1 Viewer)

Ashfaque

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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.
 
Last edited:

arnelgp

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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.
Code:
                 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

Lifelong Learner
Local time
Today, 02:54
Joined
Mar 14, 2017
Messages
5,197
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
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

Super Moderator
Staff member
Local time
Today, 19:54
Joined
Jan 20, 2009
Messages
12,310
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.
 

Ashfaque

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
How many times will i tell you.
Make the Id field in mssql, identity column.
So it will auto populate.
 

Ashfaque

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
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.

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

error reading drive A:
Local time
Today, 17:54
Joined
May 7, 2009
Messages
12,909
you can also use SQL Insert statement:
Code:
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

Student
Local time
Today, 15:24
Joined
Sep 6, 2004
Messages
757
you can also use SQL Insert statement:
Code:
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

AWF VIP
Local time
Today, 10:54
Joined
Jul 26, 2013
Messages
8,474
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.
 

Users who are viewing this thread

Top Bottom