question

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 asked thedbguy commented

Issue to code

Hi,
To code below, what should DoCmd be declared?

Dim StrSQL As String
Dim InDate As Date
Dim DatDiff As Integer

InDate = Me.FromDateTxt

StrSQL = "INSERT INTO Test (Start_Date) VALUES ('" & InDate & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True




office-vba-devoffice-access-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


I think that this code can be used in Access inside of a class or form in VBA. What code and application do you want to implement?

0 Votes 0 ·
mvpkenlin avatar image
0 Votes"
mvpkenlin answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered Jackson1990-7147 edited

Viorel,
Is there complete example to open Access table, clear details of table, in VBA?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. DoCmd is an Application object. You don't declare it, like you would a variable. You just use it.

When you say "clear details of table," are you talking about deleting all the records?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered

Hi,
I've got following error,
![93812-1g.png][1]
due to For Each line below. Please help

Public Function TblExists(ByVal strTableName As String) As Boolean
Dim tblN As Object
TblExists = False
For Each tblN In Adb.TableDefs
If tblN.Name = strTableName Then
TblExists = True
Exit For
End If
Next tblN
End Function

  Dim Adb As Object: Set Adb = CreateObject("Access.Application")
  Call Adb.OpenCurrentDatabase(F1)
  Adb.Visible = False



1g.png (5.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Try replacing this line,

For Each tblN In Adb.TableDefs

With these ones:

Dim db As DAO.CurrentDb
Set db=CurrentDb()
For Each tblN In db.TableDefs

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered

Sorry, I still get same error due to For Each line below.

  Dim db As DAO.Database
  Set db = DBEngine(0).OpenDatabase(F1)
  'db.Close
  If TblExists(ActiveSheet.Name) Then
      DoCmd.DeleteObject acTable, ActiveSheet.Name
  End If

Public Function TblExists(ByVal strTableName As String) As Boolean
Dim tblN As Object
TblExists = False
For Each tblN In db.TableDefs
If tblN.Name = strTableName Then
TblExists = True
Exit For
End If
Next tblN
End Function


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. Looks like you're all over the place on this one. That's not what I wrote, so I'm not sure if you've tried it or not.

Earlier, you didn't have ActiveSheet; but now, you do.

Please stick with one approach until we fixed it. Can you share a copy of your db? That might make it easier to help you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered thedbguy commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cool, thanks. I'll take a look at it tomorrow. It's bedtime for me now.

0 Votes 0 ·
Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered thedbguy edited

OK. Thanks. Appreciated to your help.

Any other help on this thread?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi. Is this your db?

93775-image.png


All I see in it is one table. I don't see any code. What are we supposed to fix?

0 Votes 0 ·
image.png (39.0 KiB)
Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered thedbguy commented

Hi,
In VBA, I was stuck in event (called Write_to_Text_file) to remove details of tables (named as Activesheet.name) and to flush details from empty table t1 into such table (of Access DB). Here is Excel file.
https://1drv.ms/x/s!Ai8CrEskdewXvn_aF-Ex_YazqAN3?e=bNaK4U

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good morning, I'm not sure I understand what you just said. If English is not your first language, would you mind posting what you want using your own language, so you can better describe what you mean? Thanks.

0 Votes 0 ·