-
May 25th, 2013, 07:35 PM
#1
Thread Starter
Member
Check the condition before upload Excel to Access
Access have tables:
StudentID Name Address Telephone
My Excel are:
Column A Column B Column C Column
StudentID Name Address Telephone
I want to write a condition in order to check field in My Excel file if one of Column in Excel is missing and the field in Access update one of field is missing = 0.
For Example:
My Excel are:
Coluum A Coluum B Coluum C
Name Address Telephone
John Alaska 8848421
As a result, my Access have tables in belows:
StudentID Name Address Telephone
0 John Alaska 8848421
I am appreciate for your help.
-
May 26th, 2013, 02:13 AM
#2
Re: Check the condition before upload Excel to Access
What do you want to do if there's more than 1 student ID mssing? (e.g. start at zero and then keep adding 1 for each missing ID, just put zero in all missing IDs)
(If the ID is a Key field in your Access Table then each one must be unique.)
EDIT: Here's one method you may like to try:
Code:
Option Explicit
Private Const AUTO_NUMBER As Boolean = True 'Change this to False to set missing StudentIDs to zero
Private Const AUTO_PREFIX As String = "AUTO" 'When Auto Numbering this will be the prefix (e.g. AUTO1)
Private Const AUTO_START As Long = 1 'When Auto Numbering this will be the starting value
Private db As ADODB.Connection
'
' Assumes: An open ADODB Connection named 'db'
' The Table to be updated is named 'Students'
' All columns in the Table are Character Types
' Column 'StudentID' in the Table is NOT a key field if AUTO_NUMBER is 'False'
' A Project Reference to the appropriate Excel Object Library
' A Command Button is drawn on the Form, named 'cmdExport'
' The Excel WorkBook containing the data is in 'C:\MyDir\MyWorkBook.xls'
' The Data to be exported is in the first Sheet of the WotkBook
'
Private Sub cmdExport_Click()
Dim oExcel As Object
Dim xlWbook As Excel.Workbook
Dim xlRange As Excel.Range
Dim xlWSheet As Excel.Worksheet
Dim lngRowCount As Long
Dim lngAuto As Long
Dim lngI As Long
Dim intI As Integer
Dim strData(3) As String
Dim strTable As String
Dim strFile As String
Dim strSQL As String
strTable = "Students"
strFile = "C:\MyDir\MyWorkBook.xls"
lngAuto = AUTO_START
'
' Empty the Students Table
'
strSQL = "DELETE FROM " & strTable
db.Execute strSQL
'
' Establish an Excel session, open the WorkBook
' and set the WorkSheet
'
Set oExcel = CreateObject("Excel.Application")
Set xlWbook = oExcel.Workbooks.Open(strFile)
Set xlWSheet = xlWbook.Worksheets(1)
'
' Find out how many rows are in use and set up the loop
'
lngRowCount = xlWSheet.UsedRange.Rows.Count
For lngI = 1 To lngRowCount
'
' Check if the first column is empty
' if it is then set a value depending on whether AUTO_NUMBER is set
'
Set xlRange = xlWSheet.Range("A" & CStr(lngI))
If Not IsEmpty(xlRange.Value) Then
strData(0) = xlRange.Value
Else
If AUTO_NUMBER Then
strData(0) = AUTO_PREFIX & CStr(lngAuto)
lngAuto = lngAuto + 1
Else
strData(0) = "0"
End If
End If
'
' Copy the data from the other columns into the Array
'
For intI = 1 To 3
Set xlRange = xlWSheet.Range(Chr(Asc("A") + intI) & CStr(lngI))
strData(intI) = xlRange.Value
Next intI
'
' Set up the SQL to INSERT the data into the Students Table
' and execute it
'
strSQL = "INSERT INTO "
strSQL = strSQL & strTable
strSQL = strSQL & " (StudentID, StudentName, StudentAddress, StudentNumber) Values ("
strSQL = strSQL & "'" & strData(0) & "',"
strSQL = strSQL & "'" & strData(1) & "',"
strSQL = strSQL & "'" & strData(2) & "',"
strSQL = strSQL & "'" & strData(3) & "'"
strSQL = strSQL & ")"
db.Execute strSQL
Next lngI
'
' Tidy up and exit
'
xlWbook.Close (False)
Set xlRange = Nothing
Set xlWSheet = Nothing
Set xlWbook = Nothing
Set oExcel = Nothing
db.Close
Set db = Nothing
MsgBox "Finished: " & CStr(lngRowCount) & " Rows Exported to Table: " & strTable
Unload Me
End Sub
Last edited by Doogle; May 26th, 2013 at 03:41 AM.
-
Jun 3rd, 2013, 03:15 AM
#3
Thread Starter
Member
Re: Check the condition before upload Excel to Access
Thanks Doogle. I try it out your example but the program is error "db.Execute". I wonder if you do not declare the Access directory and may be the program is not running????
-
Jun 4th, 2013, 06:49 AM
#4
Re: Check the condition before upload Excel to Access
Do you have a reference set to ADO?
Also, saying you are getting an error is not much help. There are lots of errors that could occur. What, specifically, is the error that you are getting? What does the error message say?
-
Jun 4th, 2013, 01:40 PM
#5
Re: Check the condition before upload Excel to Access
Why do you need VBA/vb6/or any code for this? You could manually check if the column exists or not and then simply import the excel file in Access Database from MS Access.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Jun 4th, 2013, 02:25 PM
#6
Re: Check the condition before upload Excel to Access
@koolsid.....you are correct, of course...importing Excel worksheets/books to MS Access is an cinch using Access. One of the reasons "I" do similar to what the OP is asking, is that I give my VB6 programs to others. I keep their DB tables (or some of them anyway) uptodate by emailing them an excel spreadsheet which they import with one of the VB6 routines I created (unually on a menu option). That way, users don't have to know Excel, nor Access, just click and go. This is PROBABLY what OP is trying to do on a learning scale....whatcha think?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|