S
siLver
Hi there,
I'm trying to do vba with access 2000. But there is couple of problem that
got me stuck..
I'm importing a number of files in a folder using DoCmd.TransferSpreadsheet,
which i loop thru the folder to get all filenames.
Question 1)
Izit possible to get the number of data imported? (eg. there is 10 xls files
to be imported, each of the files have 20lines of data, so after import my UI
can display in a label that there is 200 records imported)
But now after almost completing the project, I just found a new problem.
Before I added in other functions which compared and edit the imported data,
I had no problem in importing the data.
But today I tried again to import the files, each time I click <Import> I
get different number of records being imported.(thou some time the number of
records is correct)
Question 2)
1st flush my tables using SQL query:
Set cn = CreateObject("ADODB.connection")
cn.Open CurrentProject.Connection
strSQL = "DELETE [tbl].* FROM [tbl];"
cn.Execute strSQL
cn.Close
Set cn = Nothing
Then I start import using "DoCmd.TransferSpreadsheet"
Iz there any thing that I had missed out, thatz y my connection seem v
unstable?
Question 3)
I liked to find out, in order for a function to be 'call' in the correct
way. Must I used the keyword 'Call'?
Function MyMethod()
.........
End Function
Private Sub Main()
--> MyMethod or Call MyMethod [Which is the right way, as both seem workable]
End Sub
Question 4)
What is the right way to 'Close' my connection?
-->
Function MyFunction()
On Error GoTo Err_MyFunction
Dim db As Database
Dim qrs As Recordset
Dim qdf As QueryDef
Dim strQuery As String
Set db = DBEngine.Workspaces(0).Databases(0)
strQuery = "SELECT [tbl].name FROM [tbl];"
Set qdf = db.CreateQueryDef("", strQuery)
Set qrs = qdf.OpenRecordset()
qrs.MoveFirst
While Not qrs.EOF
..................
..................
qrs.Move 1
Wend
End_MyFunction:
[Iz this how I close and the connections? Or did I missed out some thing? Or
there is better/other ways?]
[Iz qrs.Close/qdf.Close/db.Close required?]
Set qrs = Nothing
Set qdf = Nothing
Set db = Nothing
Exit Function
Err_MyFunction:
MsgBox Err.Number & "," & Err.Description
Resume End_MyFunction
End Function
Help me pls!!
Thanx a Million..
RegarDx..
I'm trying to do vba with access 2000. But there is couple of problem that
got me stuck..
I'm importing a number of files in a folder using DoCmd.TransferSpreadsheet,
which i loop thru the folder to get all filenames.
Question 1)
Izit possible to get the number of data imported? (eg. there is 10 xls files
to be imported, each of the files have 20lines of data, so after import my UI
can display in a label that there is 200 records imported)
But now after almost completing the project, I just found a new problem.
Before I added in other functions which compared and edit the imported data,
I had no problem in importing the data.
But today I tried again to import the files, each time I click <Import> I
get different number of records being imported.(thou some time the number of
records is correct)
Question 2)
1st flush my tables using SQL query:
Set cn = CreateObject("ADODB.connection")
cn.Open CurrentProject.Connection
strSQL = "DELETE [tbl].* FROM [tbl];"
cn.Execute strSQL
cn.Close
Set cn = Nothing
Then I start import using "DoCmd.TransferSpreadsheet"
Iz there any thing that I had missed out, thatz y my connection seem v
unstable?
Question 3)
I liked to find out, in order for a function to be 'call' in the correct
way. Must I used the keyword 'Call'?
Function MyMethod()
.........
End Function
Private Sub Main()
--> MyMethod or Call MyMethod [Which is the right way, as both seem workable]
End Sub
Question 4)
What is the right way to 'Close' my connection?
-->
Function MyFunction()
On Error GoTo Err_MyFunction
Dim db As Database
Dim qrs As Recordset
Dim qdf As QueryDef
Dim strQuery As String
Set db = DBEngine.Workspaces(0).Databases(0)
strQuery = "SELECT [tbl].name FROM [tbl];"
Set qdf = db.CreateQueryDef("", strQuery)
Set qrs = qdf.OpenRecordset()
qrs.MoveFirst
While Not qrs.EOF
..................
..................
qrs.Move 1
Wend
End_MyFunction:
[Iz this how I close and the connections? Or did I missed out some thing? Or
there is better/other ways?]
[Iz qrs.Close/qdf.Close/db.Close required?]
Set qrs = Nothing
Set qdf = Nothing
Set db = Nothing
Exit Function
Err_MyFunction:
MsgBox Err.Number & "," & Err.Description
Resume End_MyFunction
End Function
Help me pls!!
Thanx a Million..
RegarDx..