Using VB and not VBA in Access

L

Larry Linson

Is there a way I can use VB code
and not VBA code in Access?
Click to expand...

No. But the core language is identical in classic VB and in VBA, so I am not
sure why one would want to use VB instead of VBA.
The reason I want to do that is that
I want to export a table in a very
specific way to a .txt file, and I don't
want to use a wizard to do that,
because I want to automate the process.
Click to expand...

VBA has all the capability to do what you want... builtin File I/O
statements, just like VB. For your information the VBA6 library is part of
VB6 and contains the core lanuage of VB6. What features or functions did you
think would be lacking?

VBA is not dependent on Wizards -- I'm not sure where anyone would get the
idea that it is.

Larry Linson
Microsoft Access MVP
 
A

Amir

Hello!

Is there a way I can use VB code and not VBA code in Access?

The reason I want to do that is that I want to export a table in a very
specific way
to a .txt file, and I don't want to use a wizard to do that, because I want
to automate the process.

Thanks for reading, and for your help!
 
A

Albert D. Kallal

As the wonderful Mr. Larry in this thread has already noted, the VBA
language in terms of syntax is actually the same language as VB6

Hence, if you know how to code in VB6, then that same code will likely work
in ms-access with little if any modifications.

So, no, you don't have to use the ms-access interface to export data to a
text file.

However, you can certainly use some of the features of ms-access to help you
in that code.

In fact, you can setup a export spec that allows you export as a fixed
width, or one of many types of different delimited data types.

You might check out the transferText command in the help. This command is
what the wizards use, and is at your
disposal in code.

In fact, you can even by-pass that command, and write your own code that
direct exports the data in a raw format.

Not knowing any details, I not sure which approach will suite your needs.

However, please do feel free to expand on what you need or trying to do...as
we are egar to help you!!
 
A

Amir

Hi!

Thanks for the answers!

What I am trying to do is this:

I have a query named qryNumbers which produces a list of numbers, like this:
1234
54678
2345
and so on.


I want to make a file named "mysqlfile.sql" which will finally contain one
long line who looks like this:

sqlprefix (" '1234','54678','2345' " ) sqlsuffix.

The purpse of this thing is to use this sql file to open in Orcale
Discoverer, and I want the process to be automatic as possible,
this is why I don't want to use any wizard.


Thanks for your help!
 
A

Albert D. Kallal

Good enough. Here is a code shell that should get you going:

You can either paste this code into a standard module, and run (call rtest
from debugger window, or whack f5).

Or, place the code in a nice form..and have it execute when a button is
pressed (you could prompt for the output file name, or even
pop up the file browse window to make this fancy..but that is up to you!).

I don't know what kind of coding experience you have...but here goes!

Warning:....the following is air code..and was typed in "one pass" (less
then 4 minutes to type this in!).

Here goes:

Public Sub rTEST()

Dim rstQryData As DAO.Recordset
Dim strSql As String


Dim strOneLine As String
Dim strFOUT As String
Dim intFOUT As Integer

Dim sqlprefix As String
Dim sqlsuffix As String


sqlprefix = "" ' set your prefix here (you could even read a query
text if you wish)
sqlsuffix = ""

strFOUT = "c:\data\mysqlfile.sql" ' output file name

strSql = "select rankField from tblData order By rankField"

' repleace the above sql with your choice. You can also
' use a query name here, for exmaple we could usew

strSql = "qryMyExportFieldThatisAlreadySorted"

' regardless, I assume the field you export is the
' first field in the query!

Set rstQryData = CurrentDb.OpenRecordset(strSql)

If rstQryData.RecordCount = 0 Then

MsgBox "no records exported"

Else

' export the data....

Do While rstQryData.EOF = False

If strOneLine <> "" Then
strOneLine = strOneLine & ","
End If

strOneLine = strOneLine & "'" & rstQryData(0) & "'"

rstQryData.MoveNext
Loop

strOneLine = sqlprefix & "(" & strOneLine & ")" & sqlsuffix

' out put to text file
intFOUT = FreeFile()
Open strFOUT For Output As #intFOUT
Print #intFOUT, strOneLine
Close intFOUT

MsgBox "export done"
End If

rstQryData.Close
Set rstQryData = Nothing

End Sub


The above looks not too bad.....you have to be test it and play with it...
 
A

Albert D. Kallal

Sure, just change:

If strOneLine <> "" Then
strOneLine = strOneLine & ","
End If

strOneLine = strOneLine & "'" & rstQryData(0) & "'"

To:

if isnull(rstQueryData!number1) = false then

If strOneLine <> "" Then
strOneLine = strOneLine & ","
End If
strOneLine = strOneLine & "'" & rstQryData!Number1 & "'"
end if

if isnull(rstQueryData!number2) = false then

If strOneLine <> "" Then
strOneLine = strOneLine & ","
End If
strOneLine = strOneLine & "'" & rstQryData!number2 & "'"
end if
 
A

Amir

This is Brilliant!, and it works great. Thank you very much!
I have more question about it:

What should I do if I want to export 2 fields each time?
I can think of a stupid way of doing it, but I don't know VB that good to
think of a smart way.

The query which I use to get the results looks like this:
Number1 Number2
1234 5576
9876
7665
76544 876
754

I want the file to look like this: "sqlprefix
'1234','5576','9876','7665','76544',876','754' sqlsuffix"

Can you think of any clever way of doing it?

Kind Regards.
 
Top