عمل Data Import من ملف Excel الى قاعدة بيانات Access

<_< وهي كل مرة كذا

:o يا ناس تدرون كذا شخص كلمني وطلب مني مساعدة بسبب هذه التحويلات وتعقيدات وخاصة Excel وهدف من استخدامه: هو التعامل مع الأسهم

سهلوا على نفسكم ...

لكن على العموم كله علم :rolleyes:

Keep going B)

شكرا لك يأخ كونان ولكنى لم اطلب منك المساعدة شخصيا ولكنى اريد المساعدة من الذى سوف يمد لى يده بها

وعلى العموم هذا هو الكود الخاص بالعملية المطلوبة ولكنى كما ذكرت من قبل توجد مشكلة فى جملة ال Insert

ولا ادرى ما الحل

Imports System.Data
Imports System.Data.OleDb
Public Class Test
	Inherits System.Windows.Forms.Form
	Dim mstrInputXLSFile As String


	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		mstrInputXLSFile = Application.StartupPath + "\test.xls"
		TextBox1.Text = mstrInputXLSFile
	End Sub
	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		Dim mExcelFile As String = mstrInputXLSFile
		Dim mAccessFile As String = Application.StartupPath & "\Test.mdb"

		Dim mWorkSheet As String
		mWorkSheet = "Sheet1"

		Dim mTableName As String
		mTableName = "Terms"

		ExcelToAccess(mstrInputXLSFile, mWorkSheet, mAccessFile, mTableName)

		'Dim mDataBase As DAO.Database
		'mDataBase = DAODBEngine_definst.OpenDatabase(mExcelFile, True, False, "Excel 5.0;HDR=Yes;IMEX=1")
		'mDataBase.Execute("Select Term, Description, Comments into [;database=" & mAccessFile & "]." & mTableName & " FROM [" & mWorkSheet & "$]")
		'MsgBox("Done. Use Access to view " & mTableName)
	End Sub
	Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)

		Dim mExcelFile As String = mstrInputXLSFile
		Dim mAccessFile As String = Application.StartupPath & "\Test.mdb"

		Dim mTableName As String
		mTableName = "Terms"

		Dim mWorkSheet As String
		mWorkSheet = "Sheet1"

		''''Sub transfers all records from .xls sourcefile.sourcesheet ...
		''''... to .mdb targetfile.targettable
		''''It is assumed that the .mdb targettable definition already ...
		''''... exists, with the same number and types of fields, ...
		''''... in the same order, as the .xls worksheet columns.
		''''It does not depend on the .mdb field names being the same ...
		''''... as the .xls column headings: although it does assume ...
		''''... that the .xls columns are named.

		If Not mWorkSheet.EndsWith("$") Then
			mWorkSheet &= "$"
		End If

		Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
		Dim sourceSQL As String = "SELECT term, description, Comments FROM [" & mWorkSheet & "]"
		Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mAccessFile & ";User Id=admin;Password=;"
		Dim targetSQL As String = "SELECT term, desc, Comments FROM " & mTableName

		'use dataadapter for target and command builder to ...
		'... create insert command, including parameter collection
		Dim targetCon As New OleDbConnection(targetConStr)
		Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
		Dim cb As New OleDbCommandBuilder(targetDA)
		Dim targetCommand As OleDbCommand = cb.GetInsertCommand
		'To Know the source and target values.............
		MsgBox(targetCommand.CommandText)
		'Debug.WriteLine(targetCommand.CommandText)

		'now do the work
		Dim sourceCon As New OleDbConnection(sourceConStr)
		Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
		targetCommand.Connection.Open()
		sourceCon.Open()
		Dim sourceReader As OleDbDataReader
		sourceReader = sourceCommand.ExecuteReader()
		While sourceReader.Read() 'for each row from source
			Try
				For i As Integer = 0 To sourceReader.FieldCount - 1
					'load values into parameters

					''Only if .xls columns match exactly to .MDB Table Fields
					'targetCommand.Parameters(i).Value = sourceReader(i)

					'' If there is no Exact Match than give "targetfieldnumber" and respective Sourcefeildname or number like:

					''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldName>)
					''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldNumber>)

					targetCommand.Parameters(1).Value = sourceReader.Item("Term")
					targetCommand.Parameters(2).Value = sourceReader.Item("desc")
				Next

				'then write to target
				targetCommand.ExecuteNonQuery()

			Catch ex As OleDbException
				Dim dbe As OleDbError
				Dim strmsg As String
				For Each dbe In ex.Errors
					strmsg &= "SQL Error: " & dbe.Message & vbCrLf
				Next
				MessageBox.Show(strmsg, "OleDBException", MessageBoxButtons.OK, MessageBoxIcon.Error)
			End Try
		End While
		sourceReader.Close()
		sourceCon.Close()
		targetCommand.Connection.Close()
	End Sub

End Class